We would like to turn columns into rows:
Here is our original SQL statement:
SELECT ROC_YM,
CUM_INCM_TAX, --當月收入(2)-稅課收入
CUM_NON_INCM_TAX, --當月收入(2)-非稅課收入(含其他)
CUM_INCM_LOAN, --當月收入(2)-融資財源
CUM_TTL_INCM, --當月收入(2)-合計
CUM_TTL_PAY_AMT --當月支出(3)
FROM FMS406VE
WHERE roc_ym BETWEEN 09701 AND 10212
ORDER BY ROC_YM;
If we would like to turn columns to rows, we need to utilize UNPIVOT as bellows:
SELECT ROC_YM,
AMOUNT_TYPE,
AMOUNT
FROM FMS406VE
UNPIVOT INCLUDE NULLS(AMOUNT FOR AMOUNT_TYPE IN
(CUM_INCM_TAX AS '01', --當月收入(2)-稅課收入
CUM_NON_INCM_TAX AS '02', --當月收入(2)-非稅課收入(含其他)
CUM_INCM_LOAN AS '03', --當月收入(2)-融資財源
CUM_TTL_INCM AS '04', --當月收入(2)-合計
CUM_TTL_PAY_AMT AS '05')) --當月支出(3)
WHERE ROC_YM BETWEEN 09701 AND 10212 --年月區間
ORDER BY ROC_YM, AMOUNT_TYPE;
No comments:
Post a Comment