sql - 如何在 Amazon Redshift 中组合行

Question

这个问题在这里已经有了答案:





Can I concatenate multiple MySQL rows into one field?

(14 个回答)


3年前关闭。




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

如果原始行是:

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

结果应该是:

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

Answer 1

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

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