行转列技巧一

--优化前

SELECT * FROM(
    SELECT t.dept_name,t.dept_code,'基本值' RTYPE,SUM(t.zh_basic_value) qn,
      SUM(decode(t.mon,1,t.zh_basic_value)) mon1,
      SUM(decode(t.mon,2,t.zh_basic_value)) mon2,
      SUM(decode(t.mon,3,t.zh_basic_value)) mon3,
      SUM(decode(t.mon,4,t.zh_basic_value)) mon4,
      SUM(decode(t.mon,5,t.zh_basic_value)) mon5,
      SUM(decode(t.mon,6,t.zh_basic_value)) mon6,
      SUM(decode(t.mon,7,t.zh_basic_value)) mon7,
      SUM(decode(t.mon,8,t.zh_basic_value)) mon8,
      SUM(decode(t.mon,9,t.zh_basic_value)) mon9,
      SUM(decode(t.mon,10,t.zh_basic_value)) mon10,
      SUM(decode(t.mon,11,t.zh_basic_value)) mon11,
      SUM(decode(t.mon,12,t.zh_basic_value)) mon12 
     FROM dms_crc_area_quota t WHERE   t.blevel=1 AND t.year=2013 GROUP BY t.dept_name,t.dept_code
  UNION ALL
    SELECT t.dept_name,t.dept_code,'满分值' RTYPE,SUM(t.zh_full_value) qn,
      SUM(decode(t.mon,1,t.zh_full_value)) mon1,
      SUM(decode(t.mon,2,t.zh_full_value)) mon2,
      SUM(decode(t.mon,3,t.zh_full_value)) mon3,
      SUM(decode(t.mon,4,t.zh_full_value)) mon4,
      SUM(decode(t.mon,5,t.zh_full_value)) mon5,
      SUM(decode(t.mon,6,t.zh_full_value)) mon6,
      SUM(decode(t.mon,7,t.zh_full_value)) mon7,
      SUM(decode(t.mon,8,t.zh_full_value)) mon8,
      SUM(decode(t.mon,9,t.zh_full_value)) mon9,
      SUM(decode(t.mon,10,t.zh_full_value)) mon10,
      SUM(decode(t.mon,11,t.zh_full_value)) mon11,
      SUM(decode(t.mon,12,t.zh_full_value)) mon12 
   FROM dms_crc_area_quota t WHERE   t.blevel=1 AND t.year=2013 GROUP BY t.dept_name,t.dept_code
  UNION ALL
    SELECT '深圳供电局',0303 dept_code,'基本值' RTYPE,SUM(t.zh_basic_value) qn,
      SUM(decode(t.mon,1,t.zh_basic_value)) mon1,
      SUM(decode(t.mon,2,t.zh_basic_value)) mon2,
      SUM(decode(t.mon,3,t.zh_basic_value)) mon3,
      SUM(decode(t.mon,4,t.zh_basic_value)) mon4,
      SUM(decode(t.mon,5,t.zh_basic_value)) mon5,
      SUM(decode(t.mon,6,t.zh_basic_value)) mon6,
      SUM(decode(t.mon,7,t.zh_basic_value)) mon7,
      SUM(decode(t.mon,8,t.zh_basic_value)) mon8,
      SUM(decode(t.mon,9,t.zh_basic_value)) mon9,
      SUM(decode(t.mon,10,t.zh_basic_value)) mon10,
      SUM(decode(t.mon,11,t.zh_basic_value)) mon11,
      SUM(decode(t.mon,12,t.zh_basic_value)) mon12 
    FROM dms_crc_area_quota t WHERE   t.blevel=1 AND t.year=2013
  UNION ALL
   SELECT '深圳供电局',0303 dept_code,'满分值' RTYPE,SUM(t.zh_full_value) qn,
    SUM(decode(t.mon,1,t.zh_full_value)) mon1,
    SUM(decode(t.mon,2,t.zh_full_value)) mon2,
    SUM(decode(t.mon,3,t.zh_full_value)) mon3,
    SUM(decode(t.mon,4,t.zh_full_value)) mon4,
    SUM(decode(t.mon,5,t.zh_full_value)) mon5,
    SUM(decode(t.mon,6,t.zh_full_value)) mon6,
    SUM(decode(t.mon,7,t.zh_full_value)) mon7,
    SUM(decode(t.mon,8,t.zh_full_value)) mon8,
    SUM(decode(t.mon,9,t.zh_full_value)) mon9,
    SUM(decode(t.mon,10,t.zh_full_value)) mon10,
    SUM(decode(t.mon,11,t.zh_full_value)) mon11,
    SUM(decode(t.mon,12,t.zh_full_value)) mon12 
   FROM dms_crc_area_quota t WHERE   t.blevel=1 AND t.year=2013
  ) t2 ORDER BY t2.dept_code;

--优化后

SELECT t2.name,t.* FROM(

    SELECT  decode(a.dept_code,NULL,0303,a.dept_code) dept_code,decode(b.rn,1,'基本值',2,'满分值') RTYPE,
      SUM(decode(b.rn,1,a.zh_basic_value,2,a.zh_full_value)) qn,
      sum(decode(a.mon,1,decode(b.rn,1,a.zh_basic_value,2,a.zh_full_value))) mon1,
      sum(decode(a.mon,2,decode(b.rn,1,a.zh_basic_value,2,a.zh_full_value))) mon2,
      sum(decode(a.mon,3,decode(b.rn,1,a.zh_basic_value,2,a.zh_full_value))) mon3,
      sum(decode(a.mon,4,decode(b.rn,1,a.zh_basic_value,2,a.zh_full_value))) mon4,
      sum(decode(a.mon,5,decode(b.rn,1,a.zh_basic_value,2,a.zh_full_value))) mon5,
      sum(decode(a.mon,6,decode(b.rn,1,a.zh_basic_value,2,a.zh_full_value))) mon6,
      sum(decode(a.mon,7,decode(b.rn,1,a.zh_basic_value,2,a.zh_full_value))) mon7,
      sum(decode(a.mon,8,decode(b.rn,1,a.zh_basic_value,2,a.zh_full_value))) mon8,
      sum(decode(a.mon,9,decode(b.rn,1,a.zh_basic_value,2,a.zh_full_value))) mon9,
      sum(decode(a.mon,10,decode(b.rn,1,a.zh_basic_value,2,a.zh_full_value))) mon10,
      sum(decode(a.mon,11,decode(b.rn,1,a.zh_basic_value,2,a.zh_full_value))) mon11,
      sum(decode(a.mon,12,decode(b.rn,1,a.zh_basic_value,2,a.zh_full_value))) mon12
    from dms_crc_area_quota a,(select rownum rn from dual connect by rownum <= 2) b
    WHERE a.blevel=1 AND a.year=2013
    group BY rollup(a.dept_code),b.rn

  )t,pub_department t2 WHERE '0'||to_char(t.dept_code)=t2.code(+) ORDER BY t.dept_code;