CODEWITHSUNDEEP
sqlamazon-redshift
Dave Hazuki
Dave Hazuki

Reputation: 3

merging multiple rows into one based on id

i have the data in this format in an amazon redshift database:

id answer
1 house
1 apple
1 moon
1 money
2 123
2 xyz
2 abc

and what i am looking for would be:

id answer
1 house, apple, moon, money
2 123, xyz, abc

any idea? the thing is that i cannot hard code the answers as they will be variable, so preferably a solution that would simply scoop the answers for each id's row and put them together separated by a delimiter.

Upvotes: 0

Views: 68

Answers (2)

M. N
M. N

Reputation: 11

You can use string_agg(concat(answer,''),',') with group by so it will be like that:

select id , string_agg(concat(answer,''),',') as answer 
from table 
group by id

tested here

Edit: you don't need concatenate, you can just use string_agg(answer,',')

Upvotes: 0

eshirvana
eshirvana

Reputation: 24568

you can use aggregate function listagg:

select id , listagg(answer,',')
from table 
group by id

Upvotes: 2

Related Questions