sql - 如何使用 postgresql 转换表

Question

我想从 db 中选择这个字段：

order_id | item1_category | item2_category | item3_category
1        | book           | food           | drink

但我所拥有的只是：

order_id | item_category
1        | book
1        | food
1        | drink

我怎样才能把我的桌子变成我需要的样子？谢谢

标签： sqlpostgresqlpivot

Answer 1

一种方法是条件聚合：

select order_id,
       max(item_category) filter (where seqnum = 1) as item_category_1,
       max(item_category) filter (where seqnum = 2) as item_category_2,
       max(item_category) filter (where seqnum = 3) as item_category_3
from (select t.*,
             row_number() over (partition by order_id order by item_category) as seqnum
      from t
     ) t
group by order_id;

另一个用途array_agg()：

select order_id,
       (array_agg(item_category order by item_category))[1] as item_category_1,
       (array_agg(item_category order by item_category))[2] as item_category_2,
       (array_agg(item_category order by item_category))[3] as item_category_3
from t
group by order_id;