使用两列进行数据透视 [英] Pivoting of data using two columns

问题描述

我有数据透视格式.看起来像这样:

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        |
------------------------------------------------------------------------------------- 

我认为需要通过命令connect进行数据透视查询.

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小提琴

这篇关于使用两列进行数据透视的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！