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'
))
参考资料：
