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

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

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.

Basically the query does nothing to my data.

CodePudding user response：

It could be achieved using

CodePudding user response：

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
;