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

 

转载于:https://www.cnblogs.com/emanlee/p/11454434.html