postgresql 如何在红移中分组和连接字段
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/24502108/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
How to GROUP BY and CONCATENATE fields in redshift
提问by spats
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.
有一些解决方案 PostgreSQL，但答案中提到的功能都没有在 Redshift 中可用。
回答by rohitkulky
Well, I am a little late but the announcement about this featurehappened 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 不断添加的所有新功能。
回答by spats
== Redshift now has support for this as answered by rohitkulky, do not use this hack ==
== Redshift 现在支持这个由 rohitkulky 回答，不要使用这个 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
看起来没有直接的方法来解决这个问题。这是我为解决它所做的，只有当你知道你的 group by field 重复了多少次时，这个解决方案才有效，例如在上面的情况下它是 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;
