Oracle 拆分列为多行 Splitting string into multiple rows in Oracle
===========================
The table is as follows:
Name | Project | Error
108 test Err1, Err2, Err3
109 test2 Err1
I want to create the following:
Name | Project | Error
108 Test Err1
108 Test Err2
108 Test Err3
109 Test2 Err1
============================
Solution 1
This may be an improved way (also with regexp and connect by):
with temp as
(
select 108 Name, 'test' Project, 'Err1, Err2, Err3' Error from dual
union all
select 109, 'test2', 'Err1' from dual
)
select distinct
t.name, t.project,
trim(regexp_substr(t.error, '[^,]+', 1, levels.column_value)) as error
from
temp t,
table(cast(multiset(select level from dual connect by level <= length (regexp_replace(t.error, '[^,]+')) + 1) as sys.OdciNumberList)) levels
order by name
============================
Solution 2
with temp as (
select 108 Name, 'test' Project, 'Err1, Err2, Err3' Error from dual
union all
select 109, 'test2', 'Err1' from dual
)
SELECT distinct Name, Project, trim(regexp_substr(str, '[^,]+', 1, level)) str
FROM (SELECT Name, Project, Error str FROM temp) t
CONNECT BY instr(str, ',', 1, level - 1) > 0
order by Name
============================
============================
REF
https://stackoverflow.com/questions/14328621/splitting-string-into-multiple-rows-in-oracle
https://stackoverflow.com/questions/38371989/how-to-convert-comma-separated-values-to-rows-in-oracle