oracle 行转列+列转行
oracle实现行列转换的方法总结
长->宽,方法一:group by - case when
select MDSENO, max(case when CUTYPE = 0 then pjMECODE else null end) as cutype0, max(case when CUTYPE = 1 then pjMECODE else null end) as cutype1, max(case when CUTYPE = 2 then pjMECODE else null end) as cutype2, max(case when CUTYPE = 3 then pjMECODE else null end) as cutype3, max(case when CUTYPE = 4 then pjMECODE else null end) as cutype4, max(case when CUTYPE = 5 then pjMECODE else null end) as cutype5, max(case when CUTYPE = 6 then pjMECODE else null end) as cutype6, max(case when CUTYPE = 7 then pjMECODE else null end) as cutype7, max(case when CUTYPE = 8 then pjMECODE else null end) as cutype8, max(case when CUTYPE = 9 then pjMECODE else null end) as cutype9, max(case when CUTYPE = 10 then pjMECODE else null end) as cutype10, max(case when CUTYPE = 11 then pjMECODE else null end) as cutype11, max(case when CUTYPE = 12 then pjMECODE else null end) as cutype12, max(case when CUTYPE = 13 then pjMECODE else null end) as cutype13, max(case when CUTYPE = 14 then pjMECODE else null end) as cutype14, max(case when CUTYPE = 15 then pjMECODE else null end) as cutype15, max(case when CUTYPE = 16 then pjMECODE else null end) as cutype16, max(case when CUTYPE = 17 then pjMECODE else null end) as cutype17, max(case when CUTYPE = 18 then pjMECODE else null end) as cutype18, max(case when CUTYPE = 19 then pjMECODE else null end) as cutype19, max(case when CUTYPE = 20 then pjMECODE else null end) as cutype20, max(case when CUTYPE = 21 then pjMECODE else null end) as cutype21, max(case when CUTYPE = 22 then pjMECODE else null end) as cutype22, max(case when CUTYPE = 23 then pjMECODE else null end) as cutype23, max(case when CUTYPE = 24 then pjMECODE else null end) as cutype24, max(case when CUTYPE = 25 then pjMECODE else null end) as cutype25, --max(case when CUTYPE = 26 then pjMECODE else null end) as cutype26, max(case when CUTYPE = 99 then pjMECODE else null end) as cutype99 from ( select MDSENO, CUTYPE, LISTAGG(to_char(MECODE), ',') WITHIN GROUP(ORDER BY MECODE) AS pjMECODE --LISTAGG(to_char(counts), ',') WITHIN GROUP(ORDER BY MECODE) AS pjcounts, --LISTAGG(to_char(amounts), ',') WITHIN GROUP(ORDER BY MECODE) AS pjamounts from ( select MDSENO, CUTYPE, MECODE --, count(*) counts, sum(amount) as amounts from lu_yb_sbda_md_temp group by MDSENO, CUTYPE, MECODE order by MDSENO, CUTYPE, MECODE ) t group by MDSENO, CUTYPE ) b group by MDSENO
长->宽,方法二:pivot() within group
select * from ( select MDSENO, CUTYPE, MECODE --, count(*) counts, sum(amount) as amounts from lu_yb_sbda_md_temp group by MDSENO, CUTYPE, MECODE ) b pivot(LISTAGG(to_char(MECODE), ',') WITHIN GROUP(ORDER BY MECODE) for CUTYPE in (0 as cutype0,1 as cutype1,2,3,4,5,6,7,8,9,10,11,12,13, 14,15,16,17,18,19,20,21,22,23,24,25,99)); -- for CUTYPE in (select distinct cutype from lu_yb_sbda_md_temp)
方法二实例:
select * from (select 1 as id, 'a' as x, 3 as v from dual union select 1 as id, 'b' as x, 4 as v from dual union select 2 as id, 'a' as x, 5 as v from dual union select 2 as id, 'c' as x, 6 as v from dual union select 2 as id, 'a' as x, 7 as v from dual) t pivot(sum(v) for x in ('a' as a, 'b', 'c'));
存储过程实现动态行转列
select * from ( select hicode, hiname, total, mecode_map as mecode, rate, avgfd from lu_consu_mate_mename_0 b ) t pivot xml(max(rate) as rate, max(avgfd) as avgfd for mecode in (select distinct mecode_map from lu_consu_mate_mename_0)) order by hicode
宽表转长表
select ROWNUM CON001, MED001, CONTYPE, CONTENT, '3' SOURCE, '' OED001, '' AAE011, to_date('2020/12/1 10:40:58', 'yyyy-mm-dd hh24:mi:ss') AAE036, '' RID from ( select b.MED001, b.INDICATION, replace(b.FORMAT,',',',')||'<br>'||b.UDOSAGE UDOSAGE, b.ADVERSE_REACTIONS, b.TABOO, b.PRECAUTIONS, b.RECIPROCITY, b.OVERDOSE, b.PHARMACOKINETICS, b.PHARMACOLOGICAL from aaa b ) t unpivot(CONTENT for CONTYPE in ( INDICATION as '01',UDOSAGE as '02',ADVERSE_REACTIONS as '03', TABOO as '04',PRECAUTIONS as '05',RECIPROCITY as '06',OVERDOSE as '07', PHARMACOKINETICS as '09',PHARMACOLOGICAL as '10' ))
参考资料: