我有透视格式的数据.它看起来像这样:
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 |
-----------------------------------------
我希望将数据表示为:
-------------------------------------------------------------------------------------
| 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.
这就是我尝试做的:
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.
这里提供了一种以您想要的格式获取数据的方法:
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
参见SQL Fiddle with Demo
这篇关于使用两列透视数据的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持跟版网!