CODEWITHSUNDEEP
sqlsnowflake-cloud-data-platform
Rajalakshmi
Rajalakshmi

Reputation: 771

Split single row value to multiple rows in Snowflake

I have a table where the column data has a combination of values seperated by ';'. I would like to split them into rows for each column value.

Table data enter image description here

Now I would like to split them into multiple rows for each value like

enter image description here

I have tried using the below SQL statement.

SELECT DISTINCT COL_NAME FROM "DB"."SCHEMA"."TABLE,
LATERAL FLATTEN(INPUT=>SPLIT(COL_NAME,';'))

But the output is not as expected. Attaching the query output below.

enter image description here

Basically the query does nothing to my data.

Upvotes: 2

Views: 17545

Answers (4)

Rajalakshmi
Rajalakshmi

Reputation: 771

I was able to resolve this by using LATERAL FLATTERN like a joining table and selecting the value from it.

SELECT DISTINCT A.VALUE AS COL_NAME 
FROM "DB"."SCHEMA"."TABLE", 
LATERAL SPLIT_TO_TABLE(COL_NAME,';')A

Upvotes: 2

Simeon Pilgrim
Simeon Pilgrim

Reputation: 25903

Your first attempt was very close, you just need to access the out of the flatten, instead of the input to the flatten

so using this CTE for data:

 WITH fake_data AS (
    SELECT *
    FROM VALUES
    ('Greensboro-High Point-Winston-Salem;Norfolk-Portsmouth-Newport News;Washington, D.C.;Roanoke-Lynchburg;Richmond-Petersburg'),
    ('Knoxville'),
    ('Knoxville;Memphis;Nashville')
    v( COL_NAME)
 )

if you had aliased you tables, and accessed the parts.

 SELECT DISTINCT f.value::text as col_name
 FROM fake_data d,
    LATERAL FLATTEN(INPUT=>SPLIT(COL_NAME,';')) f
;

which is what you did in your provided answer, but via SPLIT_TO_TABLE

 SELECT DISTINCT f.value as col_name
 FROM fake_data d,
    TABLE(SPLIT_TO_TABLE(COL_NAME,';')) f
;

STRTOK_SPLIT_TO_TABLE also is the same thing:

 SELECT DISTINCT f.value as col_name
 FROM fake_data d,
    TABLE(strtok_split_to_table(COL_NAME,';')) f
;

Which can also be done via a strtok_to_array and FLATTEN that

 SELECT DISTINCT f.value as col_name
 FROM fake_data d,
    TABLE(FLATTEN(input=>STRTOK_TO_ARRAY(COL_NAME,';'))) f
;
COL_NAME
Greensboro-High Point-Winston-Salem
Norfolk-Portsmouth-Newport News
Washington, D.C.
Roanoke-Lynchburg
Richmond-Petersburg
Knoxville
Memphis
Nashville

Upvotes: 0

Anand
Anand

Reputation: 564

Looks your data has multiple delimiters , We can leverage STRTOK_SPLIT_TO_TABLE function using multiple delimiters..

STRTOK_SPLIT_TO_TABLE

 WITH data AS (
 SELECT *
    FROM VALUES
           ('Greensboro-High Point-Winston-Salem;Norfolk-Portsmouth-Newport News Washington, D.C. Roanoke-Lynchburg Richmond-Petersburg')
              v( cities))
select * 
    from data, lateral strtok_split_to_table(cities, ';-')
    order by seq, index;

Result:

enter image description here

Upvotes: 0

Lukasz Szozda
Lukasz Szozda

Reputation: 175556

It could be achieved using SPLIT_TO_TABLE table function:

This table function splits a string (based on a specified delimiter) and flattens the results into rows.

SELECT * 
FROM tab, LATERAL SPLIT_TO_TABLE(column_name, ';')

Upvotes: 5

Related Questions