0

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:

enter image description here

This is the expected result:

[After]()

I appreciate your help and ideas. Thanks in advance.

Guru Stron
  • 102,774
  • 10
  • 95
  • 132
Gracia M
  • 21
  • 2
  • 2
    Please read [ask]. Do not post code and data as images. Also please be sure to show your attempt and explanation what is missing. – Guru Stron Apr 14 '23 at 11:37
  • Does this answer your question? [Presto equivalent of MySQL group\_concat](https://stackoverflow.com/questions/44142356/presto-equivalent-of-mysql-group-concat) – astentx Apr 14 '23 at 12:11

1 Answers1

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]
Guru Stron
  • 102,774
  • 10
  • 95
  • 132
  • @GraciaM was glad to help! But be sure to compose a better question next time - see the comment to the question (P.S. you can upvote answers also wink-wink ;). – Guru Stron Apr 14 '23 at 11:43