oracle unpivot 索引_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) total8 fromtmp9 group bystudent;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) total18 fromtmp19 group by student;

pivot的使用

1 select t.*,2 (t.语+t.数+t.外+t.物) astotal3 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) datatime3 ,usercode4 ,deptcode5 ,deptname6 ,targetcode7 ,targetname8 ,sum(coalesce(day_value,0)) day_value9 ,sum(coalesce(mon_value,0)) mon_value10 ,sum(coalesce(year_value,0)) year_value11 from(12 selectdatatime,usercode,deptcode,deptname,targetcode,targetname,day_value,mon_value,year_value13 fromMOVEBI.T_GMS_MBI_TARGET_DATA14 pivot(max(targetvalue) for type in (1 day_value,2 mon_value,3 year_value)) where targetcode = '31227061')15 group byusercode16 ,deptcode17 ,deptname18 ,targetcode19 ,targetname;20 --方法二：对原始表处理

21 select *

22 from (select '20181017'datatime,23 usercode,24 deptcode,25 deptname,26 targetcode,27 targetname,28 targetvalue,29 type30 fromMOVEBI.T_GMS_MBI_TARGET_DATA31 where datatime in ('20181017', '201810')32 and targetcode = '31227061') t33 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) 成绩 fromtmp_2 funion

select student,'数学' 科目, (select 数学 from tmp_2 where student=f.student) 成绩 fromtmp_2 funion

select student,'英语' 科目, (select 英语 from tmp_2 where student=f.student) 成绩 fromtmp_2 funion

select student,'物理' 科目, (select 物理 from tmp_2 where student=f.student) 成绩 fromtmp_2 f-------------------------------------------

select student,'语文' 科目,语文 fromtmp_2union

select student,'数学' 科目,语文 fromtmp_2union

select student,'英语' 科目,语文 fromtmp_2union

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