1.行转列
转之前:
图:
1 select e.*,f.dwjc 2 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 from 18 ( 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 data 30 from 31 ( 32 select e.*,f.dwjc 33 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_name 40 ORDER BY dwjc
2.列转行,
1).用union即可
2).listagg
3).insert all into ...select ....