将多行转换为一行多列

Question

我正试图弄清楚如何创建一个SQL查询，该查询可以基于另一个具有多行的表返回一个具有多列的聚合行。首先，我将有一个相当大的表，其中有数百万行带有订单数据，其中一个item_id可以有一行带有货币USD，一行带有币种EUR。对于每一行，我还将有额外的数据，例如number_of_items。这张表看起来像这样：

Item_id Currency    Value   Nbr_of_items    Total_nbr_of_items  Item_share 
1   USD 200 100 200 0,50
1   EUR 40  20  200 0,10
1   CNY 35  80  200 0,40
2   HKD 50  60  100 0,60
2   GBP 20  40  100 0,40
3   CNY 14  50  50  1,00
4   USD 10  30  30  1,00

我的最终目标是为每个item_id创建一个表，其中有一行，最多可以容纳3种不同的货币。下表是我想创建的，但不确定我将如何创建…有什么想法吗？非常感谢您的帮助！

编辑：刚刚更新了表格，因为我希望订单中的货币基于item_share，所以最大的item_shares应该是currency_1，第二大的item_sshare应该是currency_2等等。

Item_id Currency_1  Value_1 Nbr_of_items_1  Currency_2  Value_2 Nbr_of_items_2  Currency_3  Value_3 Nbr_of_items_3
1   USD 200 100 CNY 35  80  EUR 40  20
2   HKD 50  60  GBP 20  40  NULL    NULL    NULL
3   CNY 14  50  NULL    NULL    NULL    NULL    NULL    NULL
4   USD 10  30  NULL    NULL    NULL    NULL    NULL    NULL

Answer 1

您可以使用row_number()和条件聚合：

select item_id,
max(case when seqnum = 1 then currency end) as currency_1,
max(case when seqnum = 1 then value end) as value_1,
max(case when seqnum = 1 then num_items end) as num_items_1,
max(case when seqnum = 2 then currency end) as currency_2,
max(case when seqnum = 2 then value end) as value_2,
max(case when seqnum = 2 then num_items end) as num_items_2,
max(case when seqnum = 3 then currency end) as currency_3,
max(case when seqnum = 3 then value end) as value_3,
max(case when seqnum = 3 then num_items end) as num_items_3
from (select t.*,
row_number() over (partition by item_id order by num_items desc) as seqnum
from t
) t
group by item_id;