How to transform table with postgresql

Question

I want to select this fields from db:

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

but all i have is like:

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

How I can transform my table to be like what I need? Thanks

With PostgreSQL you probably want to crosstab.

Evan Carroll
– Evan Carroll

2019-01-03 02:20:01 +00:00
Commented Jan 3, 2019 at 2:20 — Evan Carroll
– Evan Carroll, Commented Jan 3, 2019 at 2:20

Gordon Linoff · Accepted Answer · 2019-01-03 02:19:41Z

2

One method is conditional aggregation:

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;

Another uses 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;

answered Jan 3, 2019 at 2:19

Gordon Linoff

1.3m62 gold badges706 silver badges857 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Brenda Natasha Over a year ago

I try the array_agg and it works, thanks a lot for introducing me with that function :)

Collectives™ on Stack Overflow

How to transform table with postgresql

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related