1.行转列

　　转之前:

　　　　

　　

　　　　图:

1  select e.*,f.dwjc2                from 3                  (    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 4                           where  t.cymc='464b66a4-9ed0-4ba0-9f57-989058ca4b5b'      5                  ) e join fys_dic_dwjbxx_s f on e.dwmc=f.dwbsm and  yearmonth>='201601' and yearmonth<='201612'

 

 

　　　　转之后:

 

　　　　代码:(重点decode,case when)

1     SELECT 2              dwjc as  "单位",  3              nf as "年份", 4              fxdx_name as "分析对象", 5              sum(decode(yf, '01',data ,null)) as "1月",  6              sum(decode(yf, '02', data,null)) as "2月",  7              sum(decode(yf, '03', data,null)) as "3月",  8              sum(decode(yf, '04', data,null)) as "4月",  9              sum(decode(yf, '05', data,null)) as "5月", 10              sum(decode(yf, '06', data,null)) as "6月", 11              sum(decode(yf, '07', data,null)) as "7月", 12              sum(decode(yf, '08', data,null)) as "8月", 13              sum(decode(yf, '09', data,null)) as "9月", 14              sum(decode(yf, '10', data,null)) as "10月", 15              sum(decode(yf, '11', data,null)) as "11月", 16              sum(decode(yf, '12', data,null)) as "12月" 17 from18        (19            select 20                  d.fxdx_name,21                  c.dwjc,22                  c.nf,23                  c.yf,24                  (case c.datatype 25                     when 'sr' then sr_bqsj 26                     when 'ml' then lr_bqsj 27                     when 'lr' then lr_bqsj 28                     else to_number(bz3) end 29                   ) as data30            from 31            (32                select e.*,f.dwjc33                from 34                  (    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 35                           where  t.cymc='464b66a4-9ed0-4ba0-9f57-989058ca4b5b'      36                  ) e join fys_dic_dwjbxx_s f on e.dwmc=f.dwbsm and  yearmonth>='201601' and yearmonth<='201612'37             ) 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')38       )g 39       group by dwjc,nf,fxdx_name40       ORDER BY dwjc

 

2.列转行,

　　1).用union即可

　　2).listagg

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