How to GROUP BY and CONCATENATE fields in redshift

sql postgresql group-by string-concatenation amazon-redshift
spats picture spats · Jul 1, 2014 · Viewed 14.3k times · Source

How to GROUP BY and CONCATENATE fields in redshift e.g If i have table

ID   COMPANY_ID   EMPLOYEE
1    1            Anna
2    1            Bill
3    2            Carol
4    2            Dave

How can i get result like this

COMPANY_ID   EMPLOYEE
1            Anna, Bill
2            Carol, Dave

There some solutions PostgreSQL, but none of functions mentioned in answers are available in Redshift righnow.

Answer

rohitkulky picture rohitkulky · Aug 5, 2015

Well, I am a little late but the announcement about this feature happened on 3rd Aug 2015. Redshift has introduced LISTAGG window function that makes it possible to do so now. Here is a quick solution to your problem - may or may not be useful but putting it here so that people will know!

SELECT COMPANY_ID,
       LISTAGG(EMPLOYEE,', ')
WITHIN GROUP (ORDER BY EMPLOYEE)
OVER (PARTITION BY COMPANY_ID) AS EMPLOYEE
FROM YOUR_TABLE
ORDER BY COMPANY_ID

I was happy to see this feature, and many of our production scripts are up for upgrade with all the new features Redshift keeps adding.

Here is the documentation about the function

Related questions

Select first row in each GROUP BY group?

As the title suggests, I'd like to select the first row of each set of rows grouped with a GROUP BY. Specifically, if I've got a purchases table that looks like this: SELECT * FROM purchases; My Output: id | customer | total …

Read More
How to concatenate strings of a string field in a PostgreSQL 'group by' query?

I am looking for a way to concatenate the strings of a field within a group by query. So for example, I have a table: ID COMPANY_ID EMPLOYEE 1 1 Anna 2 1 Bill 3 2 Carol 4 2 Dave and I wanted to group by company_…

Read More
GROUP BY + CASE statement

I have a working query that is grouping data by hardware model and a result, but the problem is there are many "results". I have tried to reduce that down to "if result = 0 then keep as 0, else set it to 1". …

Read More