CODEWITHSUNDEEP
splitsnowflake-cloud-data-platform
Nash
Nash

Reputation: 109

Snowflake Split Columns at delimiter to Multiple Columns

I have a table as below

enter image description here

I want to split the column OWNERS at delimiter ',' and create new columns. No of columns to be created depends on owners in OWNERS column. There is no MAX count for names in OWNERS column.
Expected result is as below .

Requesting help

enter image description here

Upvotes: 1

Views: 2641

Answers (3)

Saqib Ali
Saqib Ali

Reputation: 4448

If you have data as following:

Lease Owner
L1 Benson,Greene,Lacy
L2 Walter,Amson
L3 Rustin
L4 Thomas,Justin,Greene,Lacy

You can use a Dynamic Pivot query to split comma delimited text into columns as following:

with tabular_data as (
  select *
  from leases,
  table(split_to_table(owners, ','))
)
select *
from tabular_data
pivot (min(value) for index in (ANY));

This will produce:

LEASE OWNERS SEQ 1 2 3 4
L4 Thomas,Justin,Greene,Lacy 4 Thomas Justin Greene Lacy
L3 Rustin 3 Rustin
L2 Walter,Amson 2 Walter Amson
L1 Benson,Greene,Lacy 1 Benson Greene Lacy

Upvotes: 0

Simeon Pilgrim
Simeon Pilgrim

Reputation: 26003

Data:

create table table_a as
select * from values
  ('L1','Benson,Greene,Lacy'),
  ('L2','Walter,Amson'),
  ('L3','Rustin'),
  ('L4','Thomas,Justin,Greene,Lacy')
;

hand sql as per Dave's answer:

select
  column1 as lease,
  nullif(split_part(column2,',',1),'') owner1,
  nullif(split_part(column2,',',2),'') owner2,
  nullif(split_part(column2,',',3),'') owner3,
  nullif(split_part(column2,',',4),'') owner4
from table_a;

the max number is:

select max(array_size(split(column2,','))) from table_a;
MAX(ARRAY_SIZE(SPLIT(COLUMN2,',')))
4

so lets make the first SQL dynamically:

declare
  sql text;
  maximum_count integer;
begin
  select max(array_size(split(column2,','))) into :maximum_count from table_a;
  sql := 'select column1 as lease';
  
  for i in 1 to maximum_count do
    sql := sql || ' ,nullif(split_part(column2,'','','|| i ||'),'''') owner'||i;
  end for;
  sql := sql || ' from table_a;';
  return sql;
end;
anonymous block
select column1 as lease ,nullif(split_part(column2,',',1),'') owner1 ,nullif(split_part(column2,',',2),'') owner2 ,nullif(split_part(column2,',',3),'') owner3 ,nullif(split_part(column2,',',4),'') owner4 from table_a;

so now lets call that and return those results:

declare
  sql text;
  maximum_count integer;
  res resultset;
begin
  select max(array_size(split(column2,','))) into :maximum_count from table_a;
  sql := 'select column1 as lease';
  
  for i in 1 to maximum_count do
    sql := sql || ' ,nullif(split_part(column2,'','','|| i ||'),'''') owner'||i;
  end for;
  sql := sql || ' from table_a;';
  
  res := (execute immediate :sql);
  return table (res);
end;

if you are still running in the old UI:

execute immediate $$
declare
  sql text;
  maximum_count integer;
  res resultset;
begin
  select max(array_size(split(column2,','))) into :maximum_count from test.public.table_a;
  sql := 'select column1 as lease';
  
  for i in 1 to maximum_count do
    sql := sql || ' ,nullif(split_part(column2,'','','|| i ||'),'''') owner'||i;
  end for;
  sql := sql || ' from table_a;';
  
  res := (execute immediate :sql);
  return table (res);
end;
$$
;

Upvotes: 2

Dave Welden
Dave Welden

Reputation: 1926

This works for 4 owners. For an unlimited number of owners, you will probably need to build the SQL string interactively and then EXECUTE IMMEDIATE.

Source: Pivot ANYTHING in Snowflake, Without the SQL PIVOT Function which includes information about the pivot option.

with t(lease,owners) as (
  select * from values
  ('L1','Benson,Greene,Lacy'),
  ('L2','Walter,Amson'),
  ('L3','Rustin'),
  ('L4','Thomas,Justin,Greene,Lacy')
)
select
  lease,
  iff(split_part(owners,',',1)<>'',split_part(owners,',',1),'') owner1,
  iff(split_part(owners,',',2)<>'',split_part(owners,',',2),'') owner2,
  iff(split_part(owners,',',3)<>'',split_part(owners,',',3),'') owner3,
  iff(split_part(owners,',',4)<>'',split_part(owners,',',4),'') owner4
from t;
LEASE OWNER1 OWNER2 OWNER3 OWNER4
L1 Benson Greene Lacy
L2 Walter Amson
L3 Rustin
L4 Thomas Justin Greene Lacy

Upvotes: 0

Related Questions