Pivoting of data using two columns

Question

I have data in pivoted format. It looks like this:

-----------------------------------------
| user_id |  org  |   position  | lang  |
-----------------------------------------
|   1001  |   USE |   Boss      | EN    |
|   1001  |   USD |   Bossa     | FI    |
|   1002  |   GWR |   Dim       | SV    |
|   1003  |   GGA |   DCS       | FI    |
|   1003  |   GCA |   DDD       | SV    |
-----------------------------------------

I would like to have the data represented as:

-------------------------------------------------------------------------------------
| user_id | org_fi |  position_fi |   org_en | position_en  | org_sv  | position_sv |
-------------------------------------------------------------------------------------
|  1001   |   USD  |    Bossa     |   USE    |   Boss       |         |             |
|  1002   |        |              |          |              | GWR     |  Dim        |
|  1003   |   GGA  |    DCS       |          |              | GCA     |  DDD        |
-------------------------------------------------------------------------------------

I think that a pivot query with connect by command is needed.

This is what I tried to do:

SELECT user_id, 
    org, 
    position, 
    lang, 
    ROW_NUMBER () OVER (PARTITION BY lang, user_id ORDER BY ROWID) rn
 FROM source

However, I have no idea how to go forward.

did you try to use the PIVOT command? http://www.oracle.com/technetwork/articles/sql/11g-pivot-097235.html — tbone, Aug 16 '12 at 12:39
yes, I tried to use pivot. But the problem is that I get 1,2,3,4,5.... in rn value from my query, so I am kinda stuck right now that how can I use pivot in this situation — Jaanna, Aug 16 '12 at 12:49

score 49 · Answer 1 · edited May 23 '17 at 12:26

49

PIVOT should work fine - SQL Fiddle demo _{(schema borrowed from bluefeets answer)}

SELECT *
FROM   source
PIVOT (
        MIN(org) AS org,
        MIN(position) AS position
        FOR lang
        IN('EN' AS en, 'FI' AS fi, 'SV' AS sv)
      );

edited May 23 '17 at 12:26

Community

1
1

answered Aug 16 '12 at 13:42

Peter Lang

54,264
27
148
161

7

+1 I did not know that you could pivot on two columns like that. very nice – Taryn Aug 16 '12 at 14:01
thanks..this was quite helpful to learn about pivoting two columns. – Jaanna Aug 16 '12 at 14:03

score 12 · Accepted Answer · answered Aug 16 '12 at 13:14

Here is a way to get the data in the format you want:

SELECT user_id, 
  max(case when lang = 'FI' THEN org ELSE ' ' END) org_fi,
  max(case when lang = 'FI' THEN position ELSE ' ' END) position_fi,
  max(case when lang = 'EN' THEN org ELSE ' ' END) org_en,
  max(case when lang = 'EN' THEN position ELSE ' ' END) position_en,
  max(case when lang = 'SV' THEN org ELSE ' ' END) org_sv,
  max(case when lang = 'SV' THEN position ELSE ' ' END) position_sv
FROM source
group by user_id
order by user_id

See SQL Fiddle with Demo

Good solution for pivoting text in Oracle 10g. – Tykin Feb 22 '17 at 15:44 — Tykin, Feb 22 '17 at 15:44

score 4 · Answer 3 · answered Aug 16 '12 at 13:57

11gr2 PIVOT example

SQL> create table t1
(
user_id number,
org varchar2(10),
position varchar2(10),
lang varchar2(10)
)
Table created.
SQL> insert into t1 values (1,'ABC','VVV','EN')
1 row created.
SQL> insert into t1 values (1,'DEF','WWW','FI')
1 row created.
SQL> insert into t1 values (2,'GHI','XXX','FI')
1 row created.
SQL> insert into t1 values (2,'JKL','YYY','SV')
1 row created.
SQL> insert into t1 values (3,'MNO','ZZZ','EN')
1 row created.
SQL> commit
Commit complete.
SQL> select * from t1

   USER_ID ORG        POSITION   LANG     
---------- ---------- ---------- ----------
         1 ABC        VVV        EN        
         1 DEF        WWW        FI       
         2 GHI        XXX        FI       
         2 JKL        YYY        SV       
         3 MNO        ZZZ        EN       

5 rows selected.
SQL> select * from t1
pivot(max(org) as org, max(position) as position for lang in ('EN' as "EN",'FI' as "FI",'SV' as "SV"))

   USER_ID EN_ORG     EN_POSITION FI_ORG     FI_POSITION SV_ORG     SV_POSITION
---------- ---------- ----------- ---------- ----------- ---------- -----------
         1 ABC        VVV         DEF        WWW                              
         2                        GHI        XXX         JKL        YYY       
         3 MNO        ZZZ                                                     

3 rows selected.

Pivoting of data using two columns

3 Answers3

Linked

Related