Oracle 字符串逗号分割转多行

select * from org_orgs where pk_org in ( SELECT regexp_substr(t.pk_org_apply,’[^,]+’,1,ROWNUM) AS org FROM hrss_hi_household_master t CONNECT BY ROWNUM<=LENGTH (t.pk_org_apply) - LENGTH (regexp_replace(t.pk_org_apply, ‘,’, ‘’))+1 )

select hm.pk_hi_household_master householdMasterId, hm.name headName, hm.householdno code, hm.address address, hm.householdtype type, hm.pk_org_apply applicableOrg, (select listagg(o.name,’,’) within GROUP (ORDER BY o.name) from org_orgs o where o.pk_org in ( SELECT regexp_substr(hm.pk_org_apply,’[^,]+’,1,ROWNUM) AS org FROM DUAL CONNECT BY ROWNUM<=LENGTH (hm.pk_org_apply) - LENGTH (regexp_replace(hm.pk_org_apply, ‘,’, ‘’))+1 )) applicableOrgName, hm.proveaddr certificate, hm.photo homePage from hrss_hi_household_master hm inner join hi_household hh on hh.pk_hi_household_master=hm.pk_hi_household_master where 1=1 --hh.pk_psndoc= –(select job.pk_psnjob from hi_psnjob job where job.lastflag=‘Y’ – and job.pk_psndoc = (select pk_psndoc from bd_psndoc where code=‘228287’ ) – ) and hm.enablestate=‘已启用’