decode行转列,case when,

1.行转列

　　转之前:

　　　　

　　

　　　　图:

 select e.*,f.dwjc
               from 
                 (    select t.cymc,t.dwmc,t.bz2,t.nf,t.yf,concat(t.nf,t.yf) yearmonth,t.sr_bqsj,t.lr_bqsj,t.bz3,(select 'ml' from dual) datatype from fys_qyjyfx_two t 
                          where  t.cymc='464b66a4-9ed0-4ba0-9f57-989058ca4b5b'      
                 ) e join fys_dic_dwjbxx_s f on e.dwmc=f.dwbsm and  yearmonth>='201601' and yearmonth<='201612'

　　　　转之后:

　　　　代码:(重点decode,case when)

    SELECT
             dwjc as  "单位", 
             nf as "年份",
             fxdx_name as "分析对象",
             sum(decode(yf, '01',data ,null)) as "1月", 
             sum(decode(yf, '02', data,null)) as "2月", 
             sum(decode(yf, '03', data,null)) as "3月", 
             sum(decode(yf, '04', data,null)) as "4月", 
             sum(decode(yf, '05', data,null)) as "5月", 
             sum(decode(yf, '06', data,null)) as "6月", 
             sum(decode(yf, '07', data,null)) as "7月", 
             sum(decode(yf, '08', data,null)) as "8月", 
             sum(decode(yf, '09', data,null)) as "9月", 
             sum(decode(yf, '10', data,null)) as "10月", 
             sum(decode(yf, '11', data,null)) as "11月", 
             sum(decode(yf, '12', data,null)) as "12月" 
from
       (
           select 
                 d.fxdx_name,
                 c.dwjc,
                 c.nf,
                 c.yf,
                 (case c.datatype 
                    when 'sr' then sr_bqsj 
                    when 'ml' then lr_bqsj 
                    when 'lr' then lr_bqsj 
                    else to_number(bz3) end 
                  ) as data
           from 
           (
               select e.*,f.dwjc
               from 
                 (    select t.cymc,t.dwmc,t.bz2,t.nf,t.yf,concat(t.nf,t.yf) yearmonth,t.sr_bqsj,t.lr_bqsj,t.bz3,(select 'ml' from dual) datatype from fys_qyjyfx_two t 
                          where  t.cymc='464b66a4-9ed0-4ba0-9f57-989058ca4b5b'      
                 ) e join fys_dic_dwjbxx_s f on e.dwmc=f.dwbsm and  yearmonth>='201601' and yearmonth<='201612'
            ) c join fys_dic_fxdx d on c.bz2=d.fys_dic_fxdx_id and (d.fys_dic_fxdx_id='49974bb3-c246-4208-af60-cf7a098a3305')
      )g 
      group by dwjc,nf,fxdx_name
      ORDER BY dwjc

2.列转行,

　　1).用union即可

　　2).listagg

　　3).insert all into ...select ....