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、首先举一个简单的例子，创建一个数据表
1 create table tmp as select * from ( 2 select '张三' student,'语文' course ,78 score from dual union all 3 select '张三','数学',87 from dual union all 4 select '张三','英语',82 from dual union all 5 select '张三','物理',90 from dual union all 6 select '李四','语文',65 from dual union all 7 select '李四','数学',77 from dual union all 8 select '李四','英语',65 from dual union all 9 select '李四','物理',85 from dual);
先使用decode或case when方法
1 select 2 student, 3 max(decode(course, '语文', score)) 语文, 4 max(decode(course, '数学', score)) 数学, 5 max(decode(course, '英语', score)) 英语, 6 max(decode(course, '物理', score)) 物理, 7 sum(score) total 8 from tmp 9 group by student; 10 ----------------------------------------- 11 select 12 student, 13 max(case when course = '语文' then score end) 语文, 14 max(case when course = '数学' then score end) 数学, 15 max(case when course = '英语' then score end) 英语, 16 max(case when course = '物理' then score end) 物理, 17 sum(score) total 18 from tmp 19 group by student;
pivot的使用
1 select t.*, 2 (t.语+t.数+t.外+t.物) as total 3 from 4 (select * 5 from tmp pivot ( max(score) for course in ('语文' as 语 , '数学' as 数, '英语' as 外,'物理' as 物) ) 6 ) 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';
这不是我们想要的结果，具体改进法法如下：
1 --方法一：对结果处理 2 select max(datatime) datatime 3 ,usercode 4 ,deptcode 5 ,deptname 6 ,targetcode 7 ,targetname 8 ,sum(coalesce(day_value,0)) day_value 9 ,sum(coalesce(mon_value,0)) mon_value 10 ,sum(coalesce(year_value,0)) year_value 11 from( 12 select datatime,usercode,deptcode,deptname,targetcode,targetname,day_value,mon_value,year_value 13 from MOVEBI.T_GMS_MBI_TARGET_DATA 14 pivot(max(targetvalue) for type in (1 day_value,2 mon_value,3 year_value)) where targetcode = '31227061') 15 group by usercode 16 ,deptcode 17 ,deptname 18 ,targetcode 19 ,targetname; 20 --方法二：对原始表处理 21 select * 22 from (select '20181017' datatime, 23 usercode, 24 deptcode, 25 deptname, 26 targetcode, 27 targetname, 28 targetvalue, 29 type 30 from MOVEBI.T_GMS_MBI_TARGET_DATA 31 where datatime in ('20181017', '201810') 32 and targetcode = '31227061') t 33 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
