oracle根据某个字段拆分成多条数据

记一次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'

未拆分前的数据：

拆分后的数据：