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

Question

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?

Answer 1

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