记一次ORACLE根据某个字段分割，将一条数据拆分成多条
select
v.objid,
v.MAINPROJECT
from (SELECT objid as objid,
substr(replace(MAINPROJECT,',',','),l,instr(replace(MAINPROJECT,',',',')||',',',',l)-l) as MAINPROJECT
from
(select
v.objid as objid,
v.MAINPROJECT as MAINPROJECT
from V_SSJ_OBJ_ALL v
having count(1)>=1
group by v.MAINPROJECT,v.objid )A,
(
(SELECT LEVEL l
FROM DUAL
CONNECT BY LEVEL <=(select max((length(MAINPROJECT)) - LENGTH(REGEXP_REPLACE(REPLACE(MAINPROJECT, ',', '@'), '[^@]+', '')))
from V_SSJ_OBJ_ALL)))
WHERE substr(','||replace(MAINPROJECT,',',','),l,1)=',') v
left join V_SSJ_OBJ_ALL a on a.objid=v.objid
where v.objid='ABB21559E5044ECF86F2792BF096B625'
未拆分前的数据：
拆分后的数据：
