Split one row into multiple rows based on comma-separated string column

Question

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.

Answer 1

Use unnest on the array returned by split.

SELECT a,split_b 
FROM tbl
CROSS JOIN UNNEST(SPLIT(b,',')) AS t (split_b)