I have a table as follows in Snowflake:

What I am trying to do is split column B and C into new rows so each record is on its own. Like so:

I have tried using the below, and it is close to the solution unfortunately I am getting each record in B attached to each record in C. Whilst I want to keep them individual, so 2 = Tree, then 3 = Metal and so on.

This is my current approach and the result:

SELECT A, Y.VALUE, Z.VALUE
FROM TEST,
LATERAL split_to_table(B, ',')Y,
LATERAL split_to_table(C, ',')Z;

CodePudding user response：

You can flatten them out in separate table expressions in a CTE and join them on the sequence and index:

create or replace transient table T1 (A string, B string, C string);
insert into T1 (A, B, C) values 
('Red', '2,3,4','Tree,Metal,House'),
('Green', '5,9,10','Building,Branch,Hose'),
('Blue','10,11,12,14','Roof,Tile,Van,Car')
;


with B as
(
select A, SEQ, INDEX, VALUE as B from T1, table(split_to_table(B, ','))
), C as
(
select SEQ, INDEX, VALUE as C from T1, table(split_to_table(C, ','))
)
select A, B, C from B left join C on B.SEQ = C.SEQ and B.INDEX = C.INDEX;

CodePudding user response：

Assuming B and C have the same number of elements, you could just use the index from the first lateral join to get the corresponding elements in C

select a, y.value as b, split_part(c,',',y.index) as c
from t,
lateral split_to_table(b, ',') y