29

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.

Community
  • 1
  • 1
spats
  • 765
  • 1
  • 9
  • 12
  • 2
    Thanks for clearly identifying that you're using Redshift, not just saying "postgresql". – Craig Ringer Jul 01 '14 at 03:48
  • Group by: http://docs.aws.amazon.com/redshift/latest/dg/r_GROUP_BY_clause.html Concatenate: http://docs.aws.amazon.com/redshift/latest/dg/r_concat_op.html – Jenn Jul 01 '14 at 15:09
  • @jenn i don't concat operator can be applied on group by, its more for concatenate fields in same row e.g first & last name as shown in the link u shared. It would be great if can share complete query, if u think its possible. – spats Jul 02 '14 at 02:23

2 Answers2

40

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

rohitkulky
  • 1,112
  • 10
  • 23
-2

== 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.name
from employee e1, employee e2
where 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.name
from employee e1, employee e2, employee e3
where e1.company_id = e2.company_id and e1.company_id = e2.company_id and e1.id < e2.id and e2.id < e3.id;
spats
  • 765
  • 1
  • 9
  • 12