oracle列转行（少行-＞多行），逗号分隔字符串拆分

 原帖：https://blog.csdn.net/weixin_34553878/article/details/112824615

with test as (
    select 'aaa' id, 'bbb' name, '1,2,3' 逗号长字段 from dual 
        union all 
    select 'ccc' id, 'ddd' name, '4,5' 逗号长字段 from dual
)

select 
        t.id, t.name
        ,substr(t.逗号长字段2,instr(t.逗号长字段2, ',', 1, c.lv) + 1,instr(t.逗号长字段2, ',', 1, c.lv + 1) - (instr(t.逗号长字段2, ',', 1, c.lv) + 1)) AS 最终字段
 
from 
        (select t.*,',' || 逗号长字段 || ',' AS 逗号长字段2,length(逗号长字段 || ',') - nvl(length(REPLACE(逗号长字段, ',')), 0) AS cnt FROM test t) t
        ,(select LEVEL lv from dual CONNECT BY LEVEL <= 100) c 
        
where 
        c.lv <= t.cnt 

order by id, 最终字段