Name is required.
Email address is required.
Invalid email address
Answer is required.
Exceeding max length of 5KB

Transform list values to rows

Hello,

this is my input table:

ID value_list
1 "value_1,value_2,value_3"
2 "value_1,value_4"
3 "value_2,value_4,value_5,value_6"

and this is the result I need:

ID value
1 value_1
1 value_2
1 value_3
2 value_1
2 value_4
3 value_2
3 value_4
3 value_5
3 value_6

There are countless combinations of values possible. So each row can contain 1 to x values.
Can I reach my goal with standard components or do I need to write a python script that do the work?

Any suggestions?

1 Community Answers

Daniel Petri —

Ok, I found a way in Redshift:

with NS AS (
select 1 as n union all
select 2 union all
select 3 union all
select 4 union all
select 5 union all
select 6 union all
select 7 union all
select 8 union all
select 9 union all
select 10
)
select distinct
c.id
, TRIM(JSON_EXTRACT_ARRAY_ELEMENT_TEXT(c.value_list, NS.n - 1)) AS val
from NS
inner join input_table c ON NS.n <= JSON_ARRAY_LENGTH(c.value_list)
order by 1,2;

Works fine! :)

Post Your Community Answer

To add an answer please login