select t.dept,
t.father_type,
t.child_type,
regexp_substr(t.child_type, '[^,]+', 1, L) as c,
L
from rc_dcz_type t, (select LEVEL L from dual connect by LEVEL <= 20)
where L(+) <=
(LENGTH(t.child_type) - LENGTH(REPLACE(t.child_type, ',')) + 1)
order by t.dept, t.father_type, L
select LEVEL L from dual connect by LEVEL <= 20 level必须和connect by一起使用
产生1到20的序列数,存放到一个数据集中,做连接使用
L(+) <= (LENGTH(t.child_type) - LENGTH(REPLACE(t.child_type, ',')) + 1)
不等式右边是计算有多少个逗号,加1计算字段数量
结果图