CODEWITHSUNDEEP
snowflake-cloud-data-platformflatten
Nash
Nash

Reputation: 109

Snowflake flatten how to?

I have a scenario where data is in below format in SNOWFLAKE SQL. Column AGREEMENTS is framed using listagg

enter image description here

I would like to flatten column "AGREEMENTS" and format the table as below . Requesting help

enter image description here

Upvotes: 2

Views: 1357

Answers (2)

Mike Walton
Mike Walton

Reputation: 7339

You'd want to do something along these lines:

SELECT x.{list of fields}, y.value::varchar as agreements
FROM x,
LATERAL FLATTEN(input=>SPLIT(x.agreements,',')) y;

Upvotes: 1

Simeon Pilgrim
Simeon Pilgrim

Reputation: 25968

SPLIT_TO_TABLE is the function you are looking for. Given you have so many columns I have not typed them all out. You can use t.* to get them all.

SELECT 
    t.well,
    t.well_co,
<snip>
    a.value::text AS agreements,
    t.agreements_count
FROM table t,
    table(split_to_table(t.agreements, ',') a

Upvotes: 1

Related Questions