如何在红移中对GROUP BY和CONCATENATE字段进行分组 [英] How to GROUP BY and CONCATENATE fields in redshift

问题描述

如何在redshift中使用GROUP BY和CONCATENATE字段，例如
如果我有表格

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

我如何得到这样的结果

COMPANY_ID   EMPLOYEE
1            Anna, Bill
2            Carol, Dave

，但是在Redshift中没有提到的函数可用。

There some solutions PostgreSQL, but none of functions mentioned in answers are available in Redshift righnow.

推荐答案

看起来好像没有直接的解决方法。下面是我解决它的方法，这个解决方案只有当你知道你的团队被重复多少次，例如在上面的情况下，它的2，因为company_id被重复两次，它才起作用。在我的情况下，我知道这个计数，所以这个解决方案适用于我，虽然不是很优雅

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

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;

如果按组计数为3

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;

这篇关于如何在红移中对GROUP BY和CONCATENATE字段进行分组的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！