業(yè)務(wù)場景需求

有一張記錄操作表PLAT_OPERATE_RECORD,
其中有REPORT_ID, GROUP_ID,EXTEND1,CREATEDATE等字段,
REPORT_ID : 申請?zhí)顖驣D
GROUP_ID : 金融機構(gòu)ID
EXTEND1: 操作類型
CREATEDATE : 操作時間
前提須知: 申請?zhí)顖罂梢赃x擇兩家金融機構(gòu), 操作步驟類型有多樣, 因此會產(chǎn)生多條記錄, 現(xiàn)在要對同一個申請?zhí)顖驣D和金融機構(gòu)ID的記錄, 同時顯示操作類型的操作時間

用pivot函數(shù)將行轉(zhuǎn)為列, 但是有個問題, 這樣會產(chǎn)生多行記錄, 并且列沒有操作類型的就是空, 我們是否可以將多行壓縮為一行記錄呢?

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

可以實現(xiàn)將多列記錄聚合為一列記錄疫剃，實現(xiàn)數(shù)據(jù)的壓縮
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

結(jié)果

image