select * from(

SELECT

t.ID,

ISTATUS,

ITIMEOUT,

IRESENDTIMEOUT,

IRESENDFIXED,

IAUTOUPGRADE,

STRTERMPROP,

(

SELECT

listagg (

p.STRVALUE, ',') within group(order by p.STRVALUE)

FROM

${AppSchema}CPM_ALERT_TERMPROPERTY p

WHERE

p.ALERTID =

t.ID

) as pname,

(

SELECT

listagg ( item.STRNAME, ',') within group(order by item.STRNAME)

FROM

${AppSchema}CPM_ALERT_ALERTITEM item

WHERE

item.ALERTID = t.ID

) as

itemname,

(

SELECT

listagg ( category || '-' || refid || '-' || STRNAME, ',') within group(order by STRNAME)

FROM

(

select apg.ALERTID, apg.category,apg.refid,g.STRGROUPNAME as STRNAME from ${AppSchema}CPM_ALERT_PERSONORGROUP apg left join ${AppSchema}CPM_GROUPDEF g on apg.refid = g.id where apg.category = 'G'

union all

select apg.ALERTID, apg.category,apg.refid,p.STRUSERNAME as STRNAME from ${AppSchema}CPM_ALERT_PERSONORGROUP apg left join ${AppSchema}CPM_USERDEF p on apg.refid = p.id where apg.category = 'P'

)u

WHERE

u.ALERTID = t.ID

) as

username

FROM

${AppSchema}CPM_TERMALERT t) where 1=1