5

I am using Amazon redshift. How do I combine the result of the columns.

If the original rows are:

*ID   Name  Color
----------------
1   John   White
1   John   Black
2   Mark   Blue
2   Mark   Red*

the result should be:

*ID   Name  Color
----------------
1   John   White Black
2   Mark   Blue Red*
Ilmari Karonen
  • 47,645
  • 9
  • 89
  • 149
Mark Lorenz Vidad
  • 69
  • 2
  • 6
  • Amazon Redshift is based on Postgres. How can a MySQL answer for `group_concat` be relevant? or a "duplicate"? @FancyPants +1 – Paul Maxwell Nov 09 '17 at 07:25
  • @Used_By_Already I don't know anything about amazon redshift, but the question was and still is tagged as mysql. – fancyPants Nov 09 '17 at 09:09
  • @FancyPants good point, so I changed that. Mark: please only use tags that are relevant to this question. – Paul Maxwell Nov 09 '17 at 09:30

1 Answers1

14

Redshift provides a function LISTAGG() for what you need

SELECT id, name, LISTAGG(Color,' ') AS Colors
FROM yourtable
GROUP BY id, name

For each group in a query, the LISTAGG aggregate function orders the rows for that group according to the ORDER BY expression, then concatenates the values into a single string. http://docs.aws.amazon.com/redshift/latest/dg/r_LISTAGG.html

SELECT id, name
 , LISTAGG(Color,' ') WITHIN GROUP (ORDER BY name) AS Colors
FROM yourtable
GROUP BY id, name
Paul Maxwell
  • 28,051
  • 3
  • 30
  • 50
  • +1, it works. thanks – Mark Lorenz Vidad Nov 10 '17 at 00:59
  • 1
    Great! If you now "accept" this answer you will get 2 reputation points, and I get 15. But best of all is that other readers will know this question was answered. To Accept "[**Click the Tick**](https://ibb.co/ikqyO6)" for more see [help/accepting](https://stackoverflow.com/help/someone-answers) – Paul Maxwell Nov 10 '17 at 01:04