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=‘已启用’