How to combine rows in Amazon Redshift [duplicate]

Question

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*

Amazon Redshift is based on Postgres. How can a MySQL answer for group_concat be relevant? or a "duplicate"? @FancyPants +1 — Paul Maxwell
– Paul Maxwell, Commented Nov 9, 2017 at 7:25
@Used_By_Already I don't know anything about amazon redshift, but the question was and still is tagged as mysql. — fancyPants
– fancyPants, Commented Nov 9, 2017 at 9:09
@FancyPants good point, so I changed that. Mark: please only use tags that are relevant to this question. — Paul Maxwell
– Paul Maxwell, Commented Nov 9, 2017 at 9:30

Paul Maxwell · Accepted Answer · 2017-11-09 07:28:08Z

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

Collectives™ on Stack Overflow

How to combine rows in Amazon Redshift [duplicate]

1 Answer 1

Comments

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Linked

Related