[SQL] 用PIVOT讓資料呈現類似Excel的樞鈕分析

Oracle 11g的功能, 用PIVOT讓資料呈現類似Excel的樞鈕分析

SELECT TO_CHAR(INVOICE_DATE, 'YYYY/MM') YYMM, INVOICE_CURRENCY_CODE, SUM(INVOICE_AMOUNT) INVOICE_AMOUNT   FROM AP_INVOICES_ALL  WHERE TO_CHAR(INVOICE_DATE, 'yyyymm') BETWEEN '201201' AND '201203'    AND SET_OF_BOOKS_ID = ***  GROUP BY TO_CHAR(INVOICE_DATE, 'YYYY/MM'), INVOICE_CURRENCY_CODE  ORDER BY TO_CHAR(INVOICE_DATE, 'YYYY/MM'), INVOICE_CURRENCY_CODE;

[利用PIVOT]

SELECT *   FROM (         SELECT TO_CHAR(INVOICE_DATE, 'yyyy/mm') YYMM, INVOICE_CURRENCY_CODE, SUM(INVOICE_AMOUNT) INVOICE_AMOUNT           FROM AP_INVOICES_ALL          WHERE TO_CHAR(INVOICE_DATE, 'yyyymm') BETWEEN '201201' AND '201203'            AND SET_OF_BOOKS_ID = ***          GROUP BY TO_CHAR(INVOICE_DATE, 'yyyy/mm'), INVOICE_CURRENCY_CODE        ) PIVOT(SUM(INVOICE_AMOUNT) FOR YYMM IN ('2012/01', '2012/02', '2012/03')) ORDER BY INVOICE_CURRENCY_CODE;