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;

SELECT *
FROM TEST_PIVOT_DYNAMIC_COLUMN
;

CALL SP_PIVOT_DYNAMIC_COLUMN()
;

SELECT * FROM TEST_PIVOT_DYNAMIC_COLUMN_PV
;