SELECT
ID,
CONCAT('D', ROW_NUMBER() OVER (ORDER BY ID_LIST)) AS DUB,
TRIM(SUBSTRING_INDEX(SUBSTRING_INDEX(Mykey_LIST, ';', n.digit+1), ';', -1)) AS KEY
FROM
(SELECT 0 digit UNION ALL SELECT 1 UNION ALL SELECT 2 UNION ALL SELECT 3) n
JOIN
(
SELECT
SUBSTRING_INDEX(ID_LIST, ';', n.digit+1) AS ID,
SUBSTRING_INDEX(Mykey_LIST, ';', n.digit+1) AS Mykey_LIST
FROM
(SELECT 0 digit UNION ALL SELECT 1 UNION ALL SELECT 2 UNION ALL SELECT 3) n
CROSS JOIN
(SELECT @row_number:=0) r
JOIN
(SELECT @row_number:=@row_number+1 AS row_number, ID_LIST, Mykey_LIST
FROM your_table
ORDER BY ID_LIST) t
ON
n.digit+1 <= LENGTH(ID_LIST) - LENGTH(REPLACE(ID_LIST, ';', '')) + 1
) t
ON
t.ID != ''
ORDER BY
ID;
To Split comma separated string into rows concatenate square brackets ([]) around your string to make it into a JSON array. Then use JSON_TABLE to convert it into a table :
We need to create two CTEs one for IDs and the other for KEYs the join them together to get the expected output :
with cte_id_list as (
select ID_LIST, j.ID, row_number() over (partition by ID_LIST) as rn
from mytable t
cross join JSON_TABLE(
CONCAT('["', REPLACE(t.ID_LIST, ';', '","'), '"]'),
'$[*]'
COLUMNS (
ID int PATH '$'
)
) j
),
cte_key_list as (
select ID_LIST, j.`KEY`, row_number() over (partition by ID_LIST) as rn
from mytable t
cross join JSON_TABLE(
CONCAT('["', REPLACE(t.Mykey_LIST, ';', '","'), '"]'),
'$[*]'
COLUMNS (
`KEY` text PATH '$'
)
) j
)
select ci.ID, concat('D', dense_rank() over (order by ci.ID_LIST)) as DUB, ck.`KEY`
from cte_id_list ci
inner join cte_key_list ck on ck.ID_LIST = ci.ID_LIST and ci.rn = ck.rn
2
Answers
You can try the below query
To Split comma separated string into rows concatenate square brackets (
[]) around your string to make it into a JSON array. Then useJSON_TABLEto convert it into a table :We need to create two CTEs one for IDs and the other for KEYs the join them together to get the expected output :
Demo here