Oracle列转行SQL语句_01

Oracle列转行SQL语句_01

CREATE_TIME   COMMISSION
----------- ------------
2012/02/02         4000
2011/01/13         6500
2011/05/17         3500
2012/02/01         4000
2012/02/05         4444
2011/12/16         5000
2012/01/05         1200
2011/10/19         2800
2012/01/01         2200
查询结果如下表结构
年份  1月  2月  3月 4月  5月 6月 7月 8月 9月 10月 11月 12月
2011 6500  0       0   0   3500  0    0     0   0   2800  0     5000
2012 3400 12444 0   0       0    0    0    0    0      0     0        0
就是根据原始表的数据，统计出每年每月的营业额;
;
CREATE TABLE TEST_LYH AS
select to_date('2012/02/02','YYYY/MM/DD') AS CREATE_TIME, 4000 AS COMMISSION
FROM DUAL
UNION ALL
select to_date('2011/01/13','YYYY/MM/DD') AS CREATE_TIME, 6500 AS COMMISSION
FROM DUAL
UNION ALL
select to_date('2011/05/17','YYYY/MM/DD') AS CREATE_TIME, 3500 AS COMMISSION
FROM DUAL
UNION ALL
select to_date('2012/02/01','YYYY/MM/DD') AS CREATE_TIME, 4000 AS COMMISSION
FROM DUAL
UNION ALL
select to_date('2012/02/15','YYYY/MM/DD') AS CREATE_TIME, 4444 AS COMMISSION
FROM DUAL
UNION ALL
select to_date('2011/12/16','YYYY/MM/DD') AS CREATE_TIME, 5000 AS COMMISSION
FROM DUAL
UNION ALL
select to_date('2012/01/05','YYYY/MM/DD') AS CREATE_TIME, 1200 AS COMMISSION
FROM DUAL
UNION ALL
select to_date('2011/10/19','YYYY/MM/DD') AS CREATE_TIME, 2800 AS COMMISSION
FROM DUAL
UNION ALL
select to_date('2012/01/01','YYYY/MM/DD') AS CREATE_TIME, 2200 AS COMMISSION
FROM DUAL
;
--简单SQL

SELECT  TO_CHAR(CREATE_TIME,'YYYY') AS "年份"
       ,SUM(DECODE(TO_CHAR(CREATE_TIME,'MM'),'01',COMMISSION,0)) AS "01月"
       ,SUM(DECODE(TO_CHAR(CREATE_TIME,'MM'),'02',COMMISSION,0)) AS "02月"
       ,SUM(DECODE(TO_CHAR(CREATE_TIME,'MM'),'03',COMMISSION,0)) AS "03月"
       ,SUM(DECODE(TO_CHAR(CREATE_TIME,'MM'),'04',COMMISSION,0)) AS "04月"
       ,SUM(DECODE(TO_CHAR(CREATE_TIME,'MM'),'05',COMMISSION,0)) AS "05月"
       ,SUM(DECODE(TO_CHAR(CREATE_TIME,'MM'),'06',COMMISSION,0)) AS "06月"
       ,SUM(DECODE(TO_CHAR(CREATE_TIME,'MM'),'07',COMMISSION,0)) AS "07月"
       ,SUM(DECODE(TO_CHAR(CREATE_TIME,'MM'),'08',COMMISSION,0)) AS "08月"
       ,SUM(DECODE(TO_CHAR(CREATE_TIME,'MM'),'09',COMMISSION,0)) AS "09月"
       ,SUM(DECODE(TO_CHAR(CREATE_TIME,'MM'),'10',COMMISSION,0)) AS "10月"
       ,SUM(DECODE(TO_CHAR(CREATE_TIME,'MM'),'11',COMMISSION,0)) AS "11月"
       ,SUM(DECODE(TO_CHAR(CREATE_TIME,'MM'),'12',COMMISSION,0)) AS "12月"
FROM TEST_LYH
group by TO_CHAR(CREATE_TIME,'YYYY')

;
SELECT  CREATETION AS "年份"
       ,SUM(DATE_ROW01) AS "01月"
       ,SUM(DATE_ROW02) AS "02月"
       ,SUM(DATE_ROW03) AS "03月"
       ,SUM(DATE_ROW04) AS "04月"
       ,SUM(DATE_ROW05) AS "05月"
       ,SUM(DATE_ROW06) AS "06月"
       ,SUM(DATE_ROW07) AS "07月"
       ,SUM(DATE_ROW08) AS "08月"
       ,SUM(DATE_ROW09) AS "09月"
       ,SUM(DATE_ROW10) AS "10月"
       ,SUM(DATE_ROW11) AS "11月"
       ,SUM(DATE_ROW12) AS "12月"
FROM
(       
SELECT  CREATETION
      ,NVL(CASE
           WHEN MONTH = '01' THEN  SUM(COMMISSION) END,0)  AS DATE_ROW01
      ,NVL(CASE
           WHEN MONTH = '02' THEN  SUM(COMMISSION) END,0) AS  DATE_ROW02
      ,NVL(CASE
           WHEN MONTH = '03' THEN  SUM(COMMISSION) END,0) AS  DATE_ROW03
      ,NVL(CASE
           WHEN MONTH = '04' THEN  SUM(COMMISSION) END,0) AS  DATE_ROW04
      ,NVL(CASE
           WHEN MONTH = '05' THEN  SUM(COMMISSION) END,0) AS  DATE_ROW05
      ,NVL(CASE
           WHEN MONTH = '06' THEN  SUM(COMMISSION) END,0) AS  DATE_ROW06
      ,NVL(CASE
           WHEN MONTH = '07' THEN  SUM(COMMISSION) END,0) AS  DATE_ROW07
      ,NVL(CASE
           WHEN MONTH = '08' THEN  SUM(COMMISSION) END,0) AS  DATE_ROW08
      ,NVL(CASE
           WHEN MONTH = '09' THEN  SUM(COMMISSION) END,0) AS  DATE_ROW09
      ,NVL(CASE
           WHEN MONTH = '10' THEN  SUM(COMMISSION) END,0) AS  DATE_ROW10
      ,NVL(CASE
           WHEN MONTH = '11' THEN  SUM(COMMISSION) END,0) AS DATE_ROW11  
      ,NVL(CASE
           WHEN MONTH = '12' THEN  SUM(COMMISSION) END,0) AS DATE_ROW12               
FROM  (
SELECT TO_CHAR(CREATE_TIME,'YYYY') AS CREATETION
      ,TO_CHAR(CREATE_TIME,'MM')  AS MONTH
      ,COMMISSION,ROW_NUMBER() OVER(PARTITION BY to_char(CREATE_TIME,'YYYY') ORDER BY TO_CHAR(CREATE_TIME,'MM') ) AS RN
  FROM TEST_LYH
  )
  GROUP BY CREATETION,MONTH
  )
  GROUP BY CREATETION
  ORDER BY CREATETION

-- 刘轶鹤