有時候計算庫齡與帳齡時，當計算完後總是要做個樞紐分析，才能得到最終的結果，忽然是否想到PLSQL是否有提供這個的方法可以使用
CREATE TABLE TEST_PIVOT(
CUST_NUMBER VARCHAR2(10),
NAME VARCHAR2(10),
AR_AMOUNT NUMBER,
DAYS_PAST_DUE NUMBER
)
INSERT INTO TEST_PIVOT(CUST_NUMBER,NAME,AR_AMOUNT,DAYS_PAST_DUE)VALUES('A01','Judy',1000,5);
INSERT INTO TEST_PIVOT(CUST_NUMBER,NAME,AR_AMOUNT,DAYS_PAST_DUE)VALUES('A01','Candy',500,20);
INSERT INTO TEST_PIVOT(CUST_NUMBER,NAME,AR_AMOUNT,DAYS_PAST_DUE)VALUES('A02','Teddy',999,10);
INSERT INTO TEST_PIVOT(CUST_NUMBER,NAME,AR_AMOUNT,DAYS_PAST_DUE)VALUES('A03','Andy',651,30);
INSERT INTO TEST_PIVOT(CUST_NUMBER,NAME,AR_AMOUNT,DAYS_PAST_DUE)VALUES('A04','Ray',300,-1);
SELECT * FROM TEST_PIVOT;
SELECT CUST_NUMBER,
NAME,
AR_AMOUNT,
DAYS_PAST_DUE,
CASE
WHEN DAYS_PAST_DUE < 0 THEN '未逾期'
WHEN DAYS_PAST_DUE BETWEEN 1 AND 10 THEN '1~10天'
WHEN DAYS_PAST_DUE BETWEEN 11 AND 20 THEN '11~20天'
WHEN DAYS_PAST_DUE BETWEEN 21 AND 31 THEN '21~31天'
WHEN DAYS_PAST_DUE > 32 THEN '大於32天'
END AREA
FROM TEST_PIVOT
CUST_NUMBER NAME AR_AMOUNT DAYS_PAST_DUE AREA
-----------------------------------------------------------------
A01 Judy 1000 5 1~10天
A01 Candy 500 20 11~20天
A02 Teddy 999 10 1~10天
A03 Andy 651 30 21~31天
A04 Ray 300 -1 未逾期
SELECT CUST_NUMBER,
NAME,
AR_AMOUNT_SUM,
"'未逾期'",
"'1~10天'",
"'11~20天'",
"'21~31天'",
"'大於32天'"
FROM (SELECT PRE_PV.CUST_NUMBER,
PRE_PV.NAME,
PRE_PV.AREA,
PRE_PV.AR_AMOUNT,
SUM(PRE_PV.AR_AMOUNT) OVER(PARTITION BY CUST_NUMBER, NAME) AR_AMOUNT_SUM
FROM (SELECT CUST_NUMBER,
NAME,
AR_AMOUNT,
CASE
WHEN DAYS_PAST_DUE < 0 THEN '未逾期'
WHEN DAYS_PAST_DUE BETWEEN 1 AND 10 THEN '1~10天'
WHEN DAYS_PAST_DUE BETWEEN 11 AND 20 THEN '11~20天'
WHEN DAYS_PAST_DUE BETWEEN 21 AND 31 THEN '21~31天'
WHEN DAYS_PAST_DUE > 32 THEN '大於32天'
END AREA
FROM TEST_PIVOT) PRE_PV) PIVOT(SUM(AR_AMOUNT) FOR AREA IN('未逾期', '1~10天', '11~20天', '21~31天', '大於32天'))
CUST_NUMBER NAME AR_AMOUNT_SUM '未逾期' '1~10天' '11~20天' '21~31天' '大於32天'
A01 Judy 1000 1000
A03 Andy 651 651
A02 Teddy 999 999
A04 Ray 300 300
A01 Candy 500 500
