Search code examples
sqlsnowflake-cloud-data-platformlistagg

Snowflake LISTAGG Encapsulate Values

I was wondering if anyone has solved being able to encapsulate values in the LISTAGG function for Snowflake.

I have a table that looks something like this

ID NAME
1 PC
1 PC,A
2 ER

The following query:

SELECT
    ID,
    LISTAGG(DISTINCT NAME, ',') AS LIST
FROM TEST_TABLE

will return this table

ID LIST
1 PC,PC,A
2 ER

My expected result would be:

ID LIST
1 PC,"PC,A"
2 ER

Does anyone know how to get the expected result? I thought about testing if the value had a comma and then a CASE WHEN to switch the logic based on that.

Solution

  • We can aggregate using a CASE expression which detects commas, in which case it wraps the value in double quotes.

    SELECT
    ID,
    LISTAGG(DISTINCT CASE WHEN NAME LIKE '%,%'
                          THEN CONCAT('"', NAME, '"')
                          ELSE NAME END, ',') AS LIST
FROM TEST_TABLE;