Group multiple rows values into a single row

Question

I want to merge multiple row values for same id into a single row value separated by commas

How can do this in Athena?

This is the data:

This is the expected result:

I appreciate your help and ideas. Thanks in advance.

Answer 1

You can use array_agg aggregate function:

-- sample data
with dataset(id, interest) as(
    values (1,  'Math'),
        (1,  'Poetry'),
        (1,  'Art'),
        (2,  'Math'),
        (2,  'Other')
)

-- query
select id, array_agg(interest) interest
from dataset
group by id

Output:

id	interest
1	[Math, Poetry, Art]
2	[Math, Other]