将字符串值聚合到列表中 [英] Aggregate string values to a list

问题描述

我正在尝试一些非常简单的逻辑，以将无枢轴的列转换成实质上等于分组列表的列.但是，很难有效地做到这一点.

I am trying to work some very simple logic to transform an unpivoted column into what essentially amounts to a grouped list. However, having troubles doing this efficiently.

基本上，我有一个数据集，如下所示:

Essentially, I have a data set that looks as follows:

CUST_ID     ORDER
1           Cake
1           Bread
2           Cake
3           Cake
3           Bread
3           Croissant
4           Croissant

但是要输出如下:

CUST_ID     ORDERS
1           Cake
1           Bread, Cake
3           Cake, Bread, Croissant
4           Croissant

我尝试了子查询(无法上班)，但这仍然很残酷:

I have tried subqueries (which I cannot get to work), but this seems brutal nonetheless:

SELECT CUST_ID, SELECT (ORDER FROM table GROUP BY CUST_ID)
FROM table
GROUP BY CUSTT_ID

有什么想法吗?

推荐答案

基于

Based on this SO question, Redshift now has a LISTAGG() analytic function which you can use.

SELECT CUST_ID,
       LISTAGG("ORDER", ', ')
WITHIN GROUP (ORDER BY "ORDER")
OVER (PARTITION BY CUST_ID) AS CUST_ID
FROM Table
ORDER BY CUST_ID

这篇关于将字符串值聚合到列表中的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！