Snowflake LISTAGG Encapsulate Values-CodePudding

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.

CodePudding user response：

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;

CodePudding user response：

If I had to use listagg, I would have picked a different delimiter, like so..

select listagg(distinct name,'|')
from t;

Personally, I find array_agg easier to work with in cases like yours

select array_agg(distinct name)
from t;