Im trying to separate string values to multiple rows grouped by its id column. Most of the answers i saw include some of this functions however they are not supported by aws redshift https://docs.aws.amazon.com/redshift/latest/dg/c_unsupported-postgresql-functions.html
Assume i have a table like this
| id | order_id |
|---|---|
| 1 | 10001,10005,10006 |
| 2 | 11000,12005 |
And i would like to have a result like this
| id | order_id |
|---|---|
| 1 | 10001 |
| 1 | 10005 |
| 1 | 10006 |
| 2 | 11000 |
| 2 | 12005 |
A few concepts to have in mind. First is the recursive CTE which can be used to create number values for each position in the order_id string. Second is json functions which can split the string into parts based on commas.
A full test case with expanded input data:
create table test (id int, order_id varchar(256));
insert into test values
(1, '10001,10005,10006'),
(2, '11000,12005'),
(3, '10001,10005,10006,21000,22005'),
(4, '21000,22005,10001,10005,10006,11000,12005,10001,10005,10006,21000,22005')
;
with recursive numbers(n) as (
select 0 as n
union all
select n + 1
from numbers n
where n < (select max(length(order_id) - length(replace(order_id, ',',''))) from test)
),
input as (
select id, order_id,
length(order_id) - length(replace(order_id, ',','')) no_of_elements --counts the number of commas in the string
from test
)
select id, json_extract_array_element_text('['||order_id||']', n.n) as order_id
from input t
join numbers n
on n.n <= t.no_of_elements
order by id, order_id
;
