Total Pageviews

2014/02/13

How to use UNPIVOT to Convert Columns into Rows

Here is our original data structure:

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: