How to transform data rows into new column?

Question

table

id      text
1       aaa  
121     bbb
4       ccc 
1       ddd 

new table

id     text2
 1     aaaddd
 121   bbb
 4     ccc

I do not think I can use PIVOT since I never know how many and what id and text values would be so I cannot hardcode them in a PIVOT instruction.

Answer 1

use group by with string_agg

select id,string_agg(text,'') as text2
from table
group by id