[Oracle] string分割成multi-rows

參考來源

輸入來源如下
Name | Project | Error
108    test      Err1, Err2, Err3
109    test2     Err1
輸出結果如下
Name | Project | Error
108    Test      Err1
108    Test      Err2
108    Test      Err3
109    Test2     Err1

SQL

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