Rotate table in PostgreSQL

Question

Please help me a study case bellow: I have a table in Postgres:

Table A

and how to create a view or Table B from Table A like this:

Table B

I try to use crosstab but it's not working.

Error

Thank you so much!

https://stackoverflow.com/search?q=[postgresql]+and+([pivot]+or+[crosstab])+ — , May 25 '21 at 06:36
Thanks for your question, the order the authors is not important. may be the first is author_1, the second is author_2 .... — Tam Nguyen, May 25 '21 at 07:05
When there will be 10000 authors what the output you will expect? — astentx, May 25 '21 at 08:46

score 0 · Answer 1 · answered May 25 '21 at 11:46

You can use row_number() and conditional aggregation:

select item_id,
       max(author) filter (where seqnum = 1) as author_1,
       max(author) filter (where seqnum = 2) as author_2,
       max(author) filter (where seqnum = 3) as author_3,
       max(author) filter (where seqnum = 4) as author_4
from (select a.*,
             row_number() over (partition by item_id order by author) as seqnum
      from tablea a
     ) a
group by item_id;

score 0 · Accepted Answer · answered May 25 '21 at 11:50

I would aggregate the authors into an array, then select the array elements in an outer query:

select item_id, 
       authors[1] as author_1,
       authors[2] as author_2,
       authors[3] as author_3
from (
  select item_id, 
         array_agg(author order by author) as authors
  from items
  group by item_id
) t;

Rotate table in PostgreSQL

2 Answers2