I have a table like below with columns A(int)
and B(string)
:
A B
1 a,b,c
2 d,e
3 f,g,h
I want to create an output like below:
A B
1 a
1 b
1 c
2 d
2 e
3 f
3 g
3 h
If it helps, I am doing this in Amazon Athena (which is based on presto). I know that presto gives a function to split a string into an array. From presto docs:
split(string, delimiter) → array
Splits string on delimiter and returns an array.
Not sure how to proceed from here though.
Use unnest
on the array returned by split
.
SELECT a,split_b
FROM tbl
CROSS JOIN UNNEST(SPLIT(b,',')) AS t (split_b)