业务场景需求

有一张记录操作表PLAT_OPERATE_RECORD,
其中有REPORT_ID, GROUP_ID,EXTEND1,CREATEDATE等字段,
REPORT_ID : 申请填报ID
GROUP_ID : 金融机构ID
EXTEND1: 操作类型
CREATEDATE : 操作时间
前提须知: 申请填报可以选择两家金融机构, 操作步骤类型有多样, 因此会产生多条记录, 现在要对同一个申请填报ID和金融机构ID的记录, 同时显示操作类型的操作时间

用pivot函数将行转为列, 但是有个问题, 这样会产生多行记录, 并且列没有操作类型的就是空, 我们是否可以将多行压缩为一行记录呢?

使用 listagg() WITHIN GROUP () 将多行合并成一行(****比较常用****)

可以实现将多列记录聚合为一列记录，实现数据的压缩
listagg(measure_expr，delimiter) within group ( order by order_by_clause);
解释：
measure_expr可以是基于任何列的表达式
delimiter分隔符，默认为NULL
order_by_clause决定了列值的拼接顺序
SQL语句

SELECT T.REPORT_ID,T.GROUP_ID,
TO_CHAR(CAST(to_timestamp(LISTAGG(T.A,'') WITHIN GROUP (ORDER BY T.A)) AS DATE),'YYYY/MM/DD') A,
TO_CHAR(CAST(to_timestamp(LISTAGG(T.B,'') WITHIN GROUP (ORDER BY T.B)) AS DATE),'YYYY/MM/DD') B,
TO_CHAR(CAST(to_timestamp(LISTAGG(T.C,'') WITHIN GROUP (ORDER BY T.C)) AS DATE),'YYYY/MM/DD') C,
TO_CHAR(CAST(to_timestamp(LISTAGG(T.D,'') WITHIN GROUP (ORDER BY T.D)) AS DATE),'YYYY/MM/DD') D,
TO_CHAR(CAST(to_timestamp(LISTAGG(T.E,'') WITHIN GROUP (ORDER BY T.E)) AS DATE),'YYYY/MM/DD') E,
TO_CHAR(CAST(to_timestamp(LISTAGG(T.F,'') WITHIN GROUP (ORDER BY T.F)) AS DATE),'YYYY/MM/DD') F,
TO_CHAR(CAST(to_timestamp(LISTAGG(T.G,'') WITHIN GROUP (ORDER BY T.G)) AS DATE),'YYYY/MM/DD') G,
TO_CHAR(CAST(to_timestamp(LISTAGG(T.H,'') WITHIN GROUP (ORDER BY T.H)) AS DATE),'YYYY/MM/DD') H,
TO_CHAR(CAST(to_timestamp(LISTAGG(T.I,'') WITHIN GROUP (ORDER BY T.I)) AS DATE),'YYYY/MM/DD') I,
TO_CHAR(CAST(to_timestamp(LISTAGG(T.J,'') WITHIN GROUP (ORDER BY T.J)) AS DATE),'YYYY/MM/DD') J,
TO_CHAR(CAST(to_timestamp(LISTAGG(T.K,'') WITHIN GROUP (ORDER BY T.K)) AS DATE),'YYYY/MM/DD') K,
TO_CHAR(CAST(to_timestamp(LISTAGG(T.L,'') WITHIN GROUP (ORDER BY T.L)) AS DATE),'YYYY/MM/DD') L
FROM(
SELECT REPORT_ID,GROUP_ID,A,B,C,D,E,F,G,H,I,J,K,L FROM 
(SELECT * FROM (SELECT TP.*,ROW_NUMBER() OVER(PARTITION BY TP.REPORT_ID,TP.GROUP_ID,TP.EXTEND1 ORDER BY TP.CREATEDATE DESC) TR FROM PLAT_OPERATE_RECORD TP) WHERE TR = 1)
PIVOT (MAX(CREATEDATE) FOR extend1 in ('A' A,'B' B,'C' C,'D' D, 'E' E, 'F' F, 'G' G, 'H' H, 'I' I, 'J' J, 'K' K, 'L' L))
)T  GROUP BY T.REPORT_ID,T.GROUP_ID

结果

image