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.