Snowflake unique split_to_table

Question

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;

Answer 1

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;

A	B	C
Red	2	Tree
Red	3	Metal
Red	4	House
Green	5	Building
Green	9	Branch
Green	10	Hose
Blue	10	Roof
Blue	11	Tile
Blue	12	Van
Blue	14	Car