Snowflake SQL: concat values from multiple rows based on shared key

user_3158739 0
Viewed 1860

I have a table of values where there are a variable number of rows per each key value. I want to output a table that concats those row values together onto each distinct key value.

INPUT TABLE

KEY_ID SOURCE_VAL
1 a
1 b
1 c
2 d
3 e
3 f

Target OUTPUT TABLE

KEY_ID OUTPUT_VAL
1 a,b,c
2 d
3 e,f

What is the most efficient way to write this in Snowflake SQL?

snowflake-cloud-data-platform sql
1 Answers

It could be done with LISTAGG:

SELECT KEY_ID, 
       LISTAGG(SOURCE_VAL, ',') WITHIN GROUP(ORDER BY SOURCE_VAL) AS OUTPUT_VAL
FROM tab
GROUP BY KEY_ID
user_50708790
Related