Aggregate string values to a list

Question

I am trying to work some very simple logic to transform an unpivoted column into what essentially amounts to a grouped list. However, having troubles doing this efficiently.

Essentially, I have a data set that looks as follows:

CUST_ID     ORDER
1           Cake
1           Bread
2           Cake
3           Cake
3           Bread
3           Croissant
4           Croissant

But would like to output it as follows:

CUST_ID     ORDERS
1           Cake
1           Bread, Cake
3           Cake, Bread, Croissant
4           Croissant

I have tried subqueries (which I cannot get to work), but this seems brutal nonetheless:

SELECT CUST_ID, SELECT (ORDER FROM table GROUP BY CUST_ID)
FROM table
GROUP BY CUSTT_ID

Any ideas?

Are you using Postgres or Amazon Redshift. Although Redshift is based on (a **very** old version of) Postgres, they are not the same — , May 12 '17 at 07:38
Using Redshift, I always believed they were the same variant with only small variations. Thats interesting! — NickP, May 12 '17 at 08:09

score 3 · Accepted Answer · edited May 23 '17 at 12:34

3

Based on this SO question, Redshift now has a LISTAGG() analytic function which you can use.

SELECT CUST_ID,
       LISTAGG("ORDER", ', ')
WITHIN GROUP (ORDER BY "ORDER")
OVER (PARTITION BY CUST_ID) AS CUST_ID
FROM Table
ORDER BY CUST_ID

edited May 23 '17 at 12:34

Community

1
1

answered May 12 '17 at 07:35

Tim Biegeleisen

502,043
27
286
360

Aggregate string values to a list

1 Answers1

Linked