sql - 将多行的值连接到一列中

Question

源数据:

COLA    COLB   COLC
1       BO     AFV
1       BO     AKG
1       BO     UYD
2       BOS    KJHSDKJ
2       BOS    YWI
3       POS    JHSFJH
3       POS    IUXN

我想要的结果如下:

COLA    COLB    COLC
1       BO      AFV, AKG,UYD
2       BOS     KJHSDKJ,YWI
3       POS     JHSFJH,IUXN

COLA、COLB 是关键列。

Answer 1

这是一个可以完成这项工作的递归查询:

WITH RECURSIVE REC_VIEW (COLA, COLB, ROLL_UP, COLC) 
AS
(
SELECT COLA
     , COLB
     , MIN(COLC) (VARCHAR(1000))
     , MIN(COLC)
FROM  your_table
GROUP BY 1,2
UNION ALL
SELECT B.COLA
     , B.COLB
     , B.ROLL_UP || ',' || A.COLC
     , A.COLC
FROM   your_table A 
INNER JOIN REC_VIEW B
ON    A.COLA = B.COLA
  AND A.COLB = B.COLB
  AND A.COLC > B.COLC
)

SELECT COLA, COLB, ROLL_UP as COLC
FROM REC_VIEW

QUALIFY ROW_NUMBER() OVER (PARTITION BY COLA, COLB
                           ORDER BY CHARACTER_LENGTH(ROLL_UP) DESC) = 1

我希望我了解如何更好地格式化答案；我所有的“漂亮”间距都消失了。希望这一点是清楚的。