presto split single column value to multiple rows

user_1138192 0
Viewed 2114

I have a table on presto that has records of multiple records. From that record, I used this simple SQL query,

select id, data from my_table where id IN (1,7)

This is what I get from that query,

id        data
1   ('A', 0.0, 12)
7   ('B', 0.0, 20) ('A', 0.0, 30) ('C', 0.0, 40)

Now I want to make this data column multiple rows like below, basically, split single column value into multiple rows.

name value age
A    0.0   12
B    0.0   20
A    0.0   30
C    0.0   40

What I've tried so far, but getting an error on presto

SELECT data
FROM my_table
    CROSS APPLY STRING_SPLIT(data, ' ') where id IN (1,7);

My idea was to split the column value with space then make another split by a comma to make multiple columns at the end. Seems I need to utilize the split() and split_part() from here but I couldn't make it work. Please let me know how can I do that?

presto amazon-athena join sql
1 Answers

Using split, unnest, replace and try I am able to do the query workable now just wanted to know is there any other way to do/fix it? because I'm not fully satisfied with this way :)

WITH t AS (
select data  from my_table where id IN (1,7)
)

SELECT
  try(trim(data_parts[1])) AS name,
  try(trim(data_parts[2])) AS age,
  try(trim(data_parts[3])) AS value
FROM (
  SELECT split(replace(split_Col2,'('),',') as data_parts 
FROM t
CROSS JOIN UNNEST(SPLIT(data,')')) AS t (split_Col2) 
) WHERE length(try(trim(data_parts[1]))) > 0

Result

     name   age  value
34  'AAAA'  0.0 'NNNNN' 
36  'BBBB'  0.0 'NNNNN'     
38  'CCCC'  0.0 'MMMMM'
39  'AAAA'  0.0 'CCCCC'
user_11381920
Related