如何在redshift中GROUP BY和CONCATENATE字段

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

如何在红移中进行GROUP BY和CONCATENATE字段，例如我有桌子

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.

有一些解决方案PostgreSQL，但Redshift righnow中没有提供答案中提到的功能。

2 个解决方案

#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!

好吧，我有点晚了，但有关此功能的声明发生在2015年8月3日.Redshift引入了LISTAGG窗口功能，现在可以这样做。这是一个快速解决你的问题的方法 - 可能有用也可能没用，但把它放在这里让人们知道！

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.

我很高兴看到这个功能，我们的许多生产脚本都可以升级，Redshift不断添加所有新功能。

Here is the documentation about the function

这是关于该功能的文档

#2

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

看起来没有直接的方法来解决这个问题。以下是我要解决的问题，这个解决方案只有在你知道多少次逐个字段重复时才有效，例如在上面的情况下是2，因为company_id被重复两次。在我的情况下，我知道这个计数，所以这个解决方案适合我，虽然不是很优雅

If group by count is 2

如果按计数分组是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

如果按计数分组是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;

#1

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