How to GROUP BY and CONCATENATE fields in redshift

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 EMPLOYEEFROM YOUR_TABLEORDER 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

== Redshift now has support for this as answered by rohitkulky, do not use this hack ==

Looks like there is no straight forward way to solve this. Here is what i did to solve it, this solution works only when u know how many times ur group by field is repeated e.g in above case its 2, as company_id is being repeated twice. In my case i know this count so this solution works for me, although not very elegant

If group by count is 2

select e1.company_id, e1.name || e2.namefrom employee e1, employee e2where e1.company_id = e2.company_id and e1.id < e2.id;

If group by count is 3

select e1.company_id, e1.name || e2.name || e3.namefrom employee e1, employee e2, employee e3where e1.company_id = e2.company_id and e1.company_id = e2.company_id and e1.id < e2.id and e2.id < e3.id;