如何组合Amazon Redshift中的行[重复]

This question already has an answer here:

这个问题在这里已有答案：

• Can I concatenate multiple MySQL rows into one field? 9 answers
• 我可以将多个MySQL行连接到一个字段中吗？ 9个答案

I am using Amazon redshift. How do I combine the result of the columns.

我正在使用亚马逊红移。如何组合列的结果。

If the original rows are:

如果原始行是：

*ID   Name  Color
----------------
1   John   White
1   John   Black
2   Mark   Blue
2   Mark   Red*

the result should be:

结果应该是：

*ID   Name  Color
----------------
1   John   White Black
2   Mark   Blue Red*

1 个解决方案

#1

Redshift provides a function LISTAGG() for what you need

Redshift为您提供所需的功能LISTAGG（）

SELECT id, name, LISTAGG(Color,' ') AS Colors
FROM yourtable
GROUP BY id, name

For each group in a query, the LISTAGG aggregate function orders the rows for that group according to the ORDER BY expression, then concatenates the values into a single string. http://docs.aws.amazon.com/redshift/latest/dg/r_LISTAGG.html

对于查询中的每个组，LISTAGG聚合函数根据ORDER BY表达式对该组的行进行排序，然后将值连接成单个字符串。 http://docs.aws.amazon.com/redshift/latest/dg/r_LISTAGG.html

SELECT id, name
 , LISTAGG(Color,' ') WITHIN GROUP (ORDER BY name) AS Colors
FROM yourtable
GROUP BY id, name