我有数据转换格式.看起来像这样：
----------------------------------------- | 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 | -------------------------------------------------------------------------------------
这是我试图做的：
SELECT user_id,org,position,lang,ROW_NUMBER () OVER (PARTITION BY lang,user_id ORDER BY ROWID) rn FROM source
但是,我不知道如何前进.
解决方法
这是一种获取所需格式的数据的方法：
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
