SQL动态长度行列转置

一，案例问题描述：

某销售系统中，注册的用户会在随后的月份中购物下单，需要按月统计注册的用户中各个月下单的金额。源数据表如下：

FM:：注册月份，CM： 下单月份， AMT：下单金额

 

期望得到如下统计结果：

 

在该案列中，随着时间变化，下单月份的值是不断变化的，因此在行列转置中，需要能够满足其动态变化的要求：

 

二，准备测试数据

CREATE TABLE TEST_PIVOT_DYNAMIC_COLUMN
(
       FM DATE,
       CM DATE,
       AMT NUMBER
)
;

INSERT INTO TEST_PIVOT_DYNAMIC_COLUMN 
SELECT ADD_MONTHS(TRUNC(CURRENT_DATE, 'MM'), -3), ADD_MONTHS(TRUNC(CURRENT_DATE, 'MM'), -2), 10 FROM DUAL
;

INSERT INTO TEST_PIVOT_DYNAMIC_COLUMN 
SELECT ADD_MONTHS(TRUNC(CURRENT_DATE, 'MM'), -1), ADD_MONTHS(TRUNC(CURRENT_DATE, 'MM'), -1), 1 FROM DUAL
;
INSERT INTO TEST_PIVOT_DYNAMIC_COLUMN 
SELECT ADD_MONTHS(TRUNC(CURRENT_DATE, 'MM'), -1), ADD_MONTHS(TRUNC(CURRENT_DATE, 'MM'), 0), 2 FROM DUAL
;
INSERT INTO TEST_PIVOT_DYNAMIC_COLUMN 
SELECT ADD_MONTHS(TRUNC(CURRENT_DATE, 'MM'), 0), ADD_MONTHS(TRUNC(CURRENT_DATE, 'MM'), 0), 2 FROM DUAL
;

SELECT *
FROM TEST_PIVOT_DYNAMIC_COLUMN
;

 

三，核心转置代码

CREATE OR REPLACE PROCEDURE SP_PIVOT_DYNAMIC_COLUMN
IS
V_COLUMN VARCHAR2(1000);
BEGIN

--get the distinct value of all the month, and concatenate them together
SELECT LISTAGG(FM,',') WITHIN GROUP (ORDER BY FM) INTO V_COLUMN
FROM (
SELECT DISTINCT  'TO_DATE(''' || TO_CHAR(FM, 'YYYY/MM/DD') || ''',''YYYY/MM/DD'') AS M'  || TO_CHAR(FM, 'YYYYMM') AS FM
FROM TEST_PIVOT_DYNAMIC_COLUMN
)
;

EXECUTE IMMEDIATE
'CREATE OR REPLACE VIEW TEST_PIVOT_DYNAMIC_COLUMN_PV AS
SELECT * FROM TEST_PIVOT_DYNAMIC_COLUMN
PIVOT
(
SUM(AMT)
for CM
in ('
|| V_COLUMN ||
')
)
ORDER BY FM
';

END SP_PIVOT_DYNAMIC_COLUMN;

check the result:

SELECT *
FROM TEST_PIVOT_DYNAMIC_COLUMN
;

CALL SP_PIVOT_DYNAMIC_COLUMN()
;

SELECT * FROM TEST_PIVOT_DYNAMIC_COLUMN_PV
;