Menu
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.
665
In Data Services, the word_ext() function is capable of taking the n-th word of a string, so word_ext('green,huge,expensive', 2, ',') = 'huge'. So for our use case all we need is to call the function multiple times.
In the Split Cells dialog box, select Split to Rows or Split to Columns in the Type section as you need. And in the Specify a separator section, select the Other option, enter the comma symbol into the textbox, and then click the OK button.
Use unnest on the array returned by split.
SELECT a,split_b
FROM tbl
CROSS JOIN UNNEST(SPLIT(b,',')) AS t (split_b)
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With
