Oracle 行列转换函数pivot、unpivot的使用（二）

一、行转列pivot

关键函数pivot，其用法如下 pivot（聚合函数 for 列名 in（类型））

select * from table_name pivot(max(column_name)                            --行转列后的列的值value，聚合函数是必须要有的
                               for column_name in(value_1,value_2,value_3)     --需要行转列的列及其对应列的属性1/2/3
                                     )

1、首先举一个简单的例子，创建一个数据表

create table tmp as select * from (
select '张三' student,'语文' course ,78 score from dual union all 
select '张三','数学',87 from dual union all 
select '张三','英语',82 from dual union all 
select '张三','物理',90 from dual union all 
select '李四','语文',65 from dual union all 
select '李四','数学',77 from dual union all 
select '李四','英语',65 from dual union all 
select '李四','物理',85 from dual);

先使用decode或case when方法

select 
  student, 
  max(decode(course, '语文', score)) 语文, 
  max(decode(course, '数学', score)) 数学, 
  max(decode(course, '英语', score)) 英语, 
  max(decode(course, '物理', score)) 物理, 
  sum(score) total 
from tmp
group by student;
-----------------------------------------
select 
  student,
  max(case when course = '语文' then score end) 语文, 
  max(case when course = '数学' then score end) 数学, 
  max(case when course = '英语' then score end) 英语, 
  max(case when course = '物理' then score end) 物理, 
  sum(score) total 
from tmp
  group by student;

pivot的使用

select t.*,  
  (t.语+t.数+t.外+t.物) as total  
from  
  (select *  
  from tmp pivot ( max(score) for course in ('语文' as 语 , '数学' as 数, '英语' as 外,'物理' as 物) )  
  ) t;

结果同上

2、实际开发遇到的问题

有一张目标值表，年、月、日的值都是分开多行显示，现需合并成一行显示，具体数据如下：（type：1-->日，2-->月，3-->年；targetvalue：目标值）

select * from MOVEBI.T_GMS_MBI_TARGET_DATA where targetcode = '31227061'

 

此数据必须先进性处理，要保证数据可以聚合成一条，若直接使用会出现下列情况：

select * from MOVEBI.T_GMS_MBI_TARGET_DATA pivot(max(targetvalue) for type in (1 day_value,2 mon_value,3 year_value)) where targetcode = '31227061';

这不是我们想要的结果，具体改进法法如下：

--方法一：对结果处理
select max(datatime) datatime
      ,usercode
      ,deptcode
      ,deptname
      ,targetcode
      ,targetname
      ,sum(coalesce(day_value,0)) day_value
      ,sum(coalesce(mon_value,0)) mon_value
      ,sum(coalesce(year_value,0)) year_value
  from(
select datatime,usercode,deptcode,deptname,targetcode,targetname,day_value,mon_value,year_value
  from MOVEBI.T_GMS_MBI_TARGET_DATA
  pivot(max(targetvalue) for type in (1 day_value,2 mon_value,3 year_value)) where targetcode = '31227061')
  group by usercode
          ,deptcode
          ,deptname
          ,targetcode
          ,targetname;
--方法二：对原始表处理
select *
  from (select '20181017' datatime,
               usercode,
               deptcode,
               deptname,
               targetcode,
               targetname,
               targetvalue,
               type
          from MOVEBI.T_GMS_MBI_TARGET_DATA
         where datatime in ('20181017', '201810')
           and targetcode = '31227061') t
           pivot(max(targetvalue) for type in (1 day_value,2 mon_value,3 year_value)) where targetcode = '31227061';

二、列转行unpivot

根据上面的例子创建tmp_2测试用表

select student,科目,成绩 from tmp_2 unpivot (成绩 for 科目 in (语文, 数学, 英语, 物理));

同样不使用unpivot也可以实现同样的效果，只是sql语句会很长，而且执行速度效率也没有前者高

select student,'语文' 科目, (select 语文 from tmp_2 where student=f.student) 成绩 from tmp_2 f
union
select student,'数学' 科目, (select 数学 from tmp_2 where student=f.student) 成绩 from tmp_2 f
union
select student,'英语' 科目, (select 英语 from tmp_2 where student=f.student) 成绩 from tmp_2 f
union
select student,'物理' 科目, (select 物理 from tmp_2 where student=f.student) 成绩 from tmp_2 f
-------------------------------------------
select student,'语文' 科目,语文 from tmp_2
union
select student,'数学' 科目,语文 from tmp_2
union
select student,'英语' 科目,语文 from tmp_2
union
select student,'物理' 科目,语文 from tmp_2

 

（注：此为学习记录笔记，仅供参考若有问题请指正，后续补充......）

参考文档：https://blog.csdn.net/xiaokui_wingfly/article/details/42419207

参考文档：https://www.cnblogs.com/harvey888/p/6735093.html

参考文档：https://www.cnblogs.com/markfeifei/p/4009343.html