CODEWITHSUNDEEP
sqlsnowflake-cloud-data-platformlistagg
Grant Culp
Grant Culp

Reputation: 283

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.

Upvotes: 1

Views: 765

Answers (2)

Rajat
Rajat

Reputation: 5803

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;

Upvotes: 0

Tim Biegeleisen
Tim Biegeleisen

Reputation: 522797

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;

Upvotes: 1

Related Questions