oracle报表查询 进行行列转换

SELECT e.yearid,
       SUM(DECODE(e.monthid, '01', e.amount, 0)) month1,
       SUM(DECODE(e.monthid, '02', e.amount, 0)) month2,
       SUM(DECODE(e.monthid, '03', e.amount, 0)) month3,
       SUM(DECODE(e.monthid, '04', e.amount, 0)) month4,
       SUM(DECODE(e.monthid, '05', e.amount, 0)) month5,
       SUM(DECODE(e.monthid, '06', e.amount, 0)) month6,
       SUM(DECODE(e.monthid, '07', e.amount, 0)) month7
  FROM (
        SELECT d.yearid, d.monthid, NVL(c.amount, 0) amount
          FROM (select SUBSTR(t.monthid, 1, 4) yearid,
                        SUBSTR(t.monthid, 5) monthid,
                        t.amount
                   from SALES t) c,
                (
                 SELECT b.yearid, a.monthid
                   FROM (SELECT SUBSTR(LEVEL + 100, 2) monthid
                            FROM dual
                          CONNECT BY LEVEL < 13) a,
                         (SELECT DISTINCT SUBSTR(s.monthid, 1, 4) yearid
                            FROM sales s) b) d
         WHERE d.yearid = c.yearid(+)
           AND d.monthid = c.monthid(+)) e
 GROUP BY e.yearid
 
 ---查年的
 select distinct substr(s.monthid,1,4)yeaid from sales s;
 
 ---查12 个月
 select substr(level +100,2)monthid from dual connect by level <13;
 
 ---把年和月合成一张表
 select * from 
 (select distinct substr(s.monthid,1,4)yeaid from sales s)tab1,
 (select substr(level +100,2)monthid from dual connect by level <13)tab2;
 
 ---汇总
 select substr(tab3.monthid,1,4)year,substr(tab3.monthid,5)month,tab3.amount from (
 select s.monthid monthid,sum(s.amount) amount from sales s group by s.monthid
 )tab3


 ----联表查询
select tab4.year year,tab4.month month,tab4.amount amount from 
( select substr(tab3.monthid,1,4)year,substr(tab3.monthid,5)month,tab3.amount from (
 select s.monthid monthid,sum(s.amount) amount from sales s group by s.monthid
 )tab3)tab4,
( select substr(tab3.monthid,1,4)year,substr(tab3.monthid,5)month,tab3.amount from (
 select s.monthid monthid,sum(s.amount) amount from sales s group by s.monthid
 )tab3)tab5
 where tab4.year = tab5.year
 and tab4.month = tab5.month


 
 ---对年进行分组 和行列倒换
select tab6.year,
       sum(decode(tab6.month, '01', tab6.amount, 0)) "01",
       sum(decode(tab6.month, '02', tab6.amount, 0)) "02",
       sum(decode(tab6.month, '03', tab6.amount, 0)) "03",
       sum(decode(tab6.month, '04', tab6.amount, 0)) "04",
       sum(decode(tab6.month, '05', tab6.amount, 0)) "05"
  from (select tab4.year year, tab4.month month, tab4.amount amount
          from (select substr(tab3.monthid, 1, 4) year,
                       substr(tab3.monthid, 5) month,
                       tab3.amount
                  from (select s.monthid monthid, sum(s.amount) amount
                          from sales s
                         group by s.monthid) tab3) tab4,
               (select substr(tab3.monthid, 1, 4) year,
                       substr(tab3.monthid, 5) month,
                       tab3.amount
                  from (select s.monthid monthid, sum(s.amount) amount
                          from sales s
                         group by s.monthid) tab3) tab5
         where tab4.year = tab5.year
           and tab4.month = tab5.month) tab6
 group by tab6.year