How to Convert Oracle rows to columns

Requirement
Here has my query SQL statement.

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18

SELECT SUBSTR(PDATE, 0, 5) AS YYY_MM, ROUND(SUM(NVL(PAY_T_BILL, 0))/100000000, 0) AS PAY_T_BILL FROM FMS406VD WHERE SUBSTR(PDATE, 0, 5) IN ('10201', '10202', '10203', '10204', '10205', '10206', '10207', '10208', '10209', '10210', '10211', '10212') AND BL_ROC_YM = SUBSTR(PDATE, 0, 5) GROUP BY SUBSTR(PDATE, 0, 5) ORDER BY SUBSTR(PDATE, 0, 5)

Here is the query result

If I would like to convert row into column, how to do it?

Solution

You can make good use of PIVOT to fulfill this requirement. The SQL statement can be re-written into this way

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34

SELECT AMT1, AMT2, AMT3, AMT4, AMT5, AMT6, AMT7, AMT8, AMT9, AMT10, AMT11, AMT12 FROM( SELECT SUBSTR(PDATE, 0, 5) AS YYY_MM, ROUND(SUM(NVL(PAY_T_BILL, 0))/100000000, 0) AS PAY_T_BILL FROM FMS406VD WHERE SUBSTR(PDATE, 0, 5) IN ('10201', '10202', '10203', '10204', '10205', '10206', '10207', '10208', '10209', '10210', '10211', '10212' ) AND BL_ROC_YM = SUBSTR(PDATE, 0, 5) GROUP BY SUBSTR(PDATE, 0, 5) ORDER BY SUBSTR(PDATE, 0, 5) ) PIVOT(SUM(PAY_T_BILL) FOR YYY_MM IN ( '10201' AS AMT1, '10202' AS AMT2, '10203' AS AMT3, '10204' AS AMT4, '10205' AS AMT5, '10206' AS AMT6, '10207' AS AMT7, '10208' AS AMT8, '10209' AS AMT9, '10210' AS AMT10, '10211' AS AMT11, '10212' AS AMT12))

The query result is as bellowing:

Reference
[1] http://www.oracle.com/technetwork/articles/sql/11g-pivot-097235.html