oracle竖表转横表字段合并

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

转载于:https://www.cnblogs.com/restart30/p/10141923.html