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
-- 刘轶鹤