一，案例问题描述：

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

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_COLUMNISV_COLUMN VARCHAR2(1000);BEGIN--get the distinct value of all the month, and concatenate them togetherSELECT LISTAGG(FM,',') WITHIN GROUP (ORDER BY FM) INTO V_COLUMNFROM (SELECT DISTINCT  'TO_DATE(''' || TO_CHAR(FM, 'YYYY/MM/DD') || ''',''YYYY/MM/DD'') AS M'  || TO_CHAR(FM, 'YYYYMM') AS FMFROM TEST_PIVOT_DYNAMIC_COLUMN);EXECUTE IMMEDIATE'CREATE OR REPLACE VIEW TEST_PIVOT_DYNAMIC_COLUMN_PV ASSELECT * FROM TEST_PIVOT_DYNAMIC_COLUMNPIVOT(SUM(AMT)for CMin ('|| 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;