Snowflake Split Columns at delimiter to Multiple Columns

Question

I have a table as below

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

Answer 1

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;
$$
;