Snowflake flatten how to?

user_12328947 0
Viewed 229

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

snowflake-cloud-data-platform flatten
2 Answers

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
user_439920

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;
user_121789800
Related