Oracle sql技巧--行列转换

oracle行列转换

情形一：将查询结果转置（使用pivot和union all将上面的student表转置）

对应数据的sql

with student as
  (select 101 as sid, '张三' as sname, 10 as sage from dual union
  select 102 as sid, '李四' as sname, 20 as sage from dual union
  select 103 as sid, '王五' as sname, 30 as sage from dual)
select * from student;

图1

 

图2

注：如果不想建student表的话，就在这条sql语句的前面加上图1对应sql的前4行

select '学号' as item,
       to_char(mt1.mate1) as mate1,
       to_char(mt1.mate2) as mate2,
       to_char(mt1.mate3) as mate3
  from (select *
          from (select t1.tempType, t1.sid
                  from (select decode(s.sid,
                                      101, 'type1',
                                      102, 'type2',
                                      103, 'type3') as tempType,
                               s.sid,
                               s.sname,
                               s.sage
                          from student s) t1)
        pivot(sum(sid)
           for tempType in('type1' as mate1,
                          'type2' as mate2,
                          'type3' as mate3))) mt1
union all
select '姓名' as item, mt2.*
  from (select *
          from (select t2.tempType, t2.sname
                  from (select decode(s.sid,
                                      101, 'type1',
                                      102, 'type2',
                                      103, 'type3') as tempType,
                               s.sid,
                               s.sname,
                               s.sage
                          from student s) t2)
        pivot(max(sname)
           for tempType in('type1' as mate1,
                          'type2' as mate2,
                          'type3' as mate3))) mt2
union all
select '年龄' as item,
       to_char(mt3.mate1) as mate1,
       to_char(mt3.mate2) as mate2,
       to_char(mt3.mate3) as mate3
  from (select *
          from (select t3.tempType, t3.sage
                  from (select decode(s.sid,
                                      101, 'type1',
                                      102, 'type2',
                                      103, 'type3') as tempType,
                               s.sid,
                               s.sname,
                               s.sage
                          from student s) t3)
        pivot(sum(sage)
           for tempType in('type1' as mate1,
                          'type2' as mate2,
                          'type3' as mate3))) mt3;

附：更为简便的写法

select col as item, mate1, mate2, mate3
  from (select rn, col, val
          from (select rownum rn,
                       to_char(student.sid) as sid,
                       student.sname,
                       to_char(student.sage) as sage
                  from student) unpivot(val for col in(sid, sname, sage)))
pivot(max(val)
   for rn in(1 as mate1, 2 as mate2, 3 as mate3));

情形二（将多列转换至一行）：

上图的解释：

左图是一个临时查出的数据表，其中存放了各个项目在不同年份的统计指标值和指标完成率（datatype 1 收入 2利润）

第一行是项目301，在2017年的收入值为1000，收入指标完成率为10%

原始数据：

with proStat as  
 (select 301 as proId, '2017' as vc_year, 1 as data_type, 1000 as l_value, 10 as rateValue from dual union
  select 301 as proId, '2017' as vc_year, 2 as data_type, 2000 as l_value, 15 as rateValue from dual union
  select 302 as proId, '2018' as vc_year, 1 as data_type, 3345 as l_value, 30 as rateValue from dual union
  select 302 as proId, '2018' as vc_year, 2 as data_type, 4456 as l_value, 35 as rateValue from dual union
  select 303 as proId, '2017' as vc_year, 1 as data_type, 1100 as l_value, 80 as rateValue from dual union
  select 303 as proId, '2017' as vc_year, 2 as data_type, 1300 as l_value, 85 as rateValue from dual)  
select * from proStat;

数据转换操作：

select proId,
       vc_year           as year,
       income_myvalue    as income,
       netprofit_myvalue as netprofit,
       income_myrate     as incomeRate,
       netprofit_myrate  as netProfitRate
  from proStat
pivot(sum(l_value) as myvalue, sum(rateValue) as myrate
   for data_type in('1' as income, '2' as netProfit))
 order by proId

该技巧的搜索过程：

然后还可以参考：https://blog.csdn.net/seandba/article/details/72730657