Concat the value of multiple rows in one column

Question

SOURCE OF SOURCES:

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

My desired result is below:

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

COLA, COLB are the key columns.

Answer 1

Here is the recursive query that will complete the task:

 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 

I would like to understand how to format the answers better; all my "beautiful" intervals have disappeared. Hope this is clear.

Answer 2

Here is a link to the sample.

It has some sample data and an actual query.