Combine multiple rows into columns-CodePudding

I have a table (table1) with the following columns:

     Column       | Type |
------------------ ------ 
 activity_id      | text |
 interest_title   | text |
 interest_content | text |

I have another table (table2), with the following columns

activity_id         | character varying  |           
interest_1_title    | character varying  |
interest_1_content  | character varying  |
interest_2_title    | character varying  |
interest_2_content  | character varying  |
interest_3_title    | character varying  |
interest_3_content  | character varying  |
interest_4_title    | character varying  |
interest_4_content  | character varying  |
interest_5_title    | character varying  |
interest_5_content  | character varying  |

How do I take data from table1 and put properly into table2 (with dynamic column naming etc)?

Caveat is table1 can store less or more, than 5 rows per activity_id, so I'm only interested in first 5 occurrences.

Thanks!

CodePudding user response：

You can aggregate the titles and content into arrays, then pick the first 5:

select activity_id, 
       titles[1] as interest_1_title, 
       contents[1] as interest_1_content,
       titles[2] as interest_2_title, 
       contents[2] as interest_2_content,
       ....
from (
   select activity_id, 
          array_agg(interest_title) as titles, 
          array_agg(interest_content) as contents
   from table1
   group by activity_id
) t1