How to GROUP BY and CONCATENATE fields in redshift

Question

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 are some solutions for PostgreSQL, but none of functions mentioned in those answers are available in Redshift rightnow.

Answer 1

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