一、行转列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、首先举一个简单的例子，创建一个数据表
11 create table tmp as select * from ( 22 select '张三' student,'语文' course ,78 score from dual union all 33 select '张三','数学',87 from dual union all 44 select '张三','英语',82 from dual union all 55 select '张三','物理',90 from dual union all 66 select '李四','语文',65 from dual union all 77 select '李四','数学',77 from dual union all 88 select '李四','英语',65 from dual union all 99 select '李四','物理',85 from dual);
先使用decode或case when方法
1 1 select 2 2 student, 3 3 max(decode(course, '语文', score)) 语文, 4 4 max(decode(course, '数学', score)) 数学, 5 5 max(decode(course, '英语', score)) 英语, 6 6 max(decode(course, '物理', score)) 物理, 7 7 sum(score) total 8 8 from tmp 9 9 group by student; 1010 ----------------------------------------- 1111 select 1212 student, 1313 max(case when course = '语文' then score end) 语文, 1414 max(case when course = '数学' then score end) 数学, 1515 max(case when course = '英语' then score end) 英语, 1616 max(case when course = '物理' then score end) 物理, 1717 sum(score) total 1818 from tmp 1919 group by student;
pivot的使用
11 select t.*, 22 (t.语+t.数+t.外+t.物) as total 33 from 44 (select * 55 from tmp pivot ( max(score) for course in ('语文' as 语 , '数学' as 数, '英语' as 外,'物理' as 物) ) 66 ) 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 1 --方法一：对结果处理 2 2 select max(datatime) datatime 3 3 ,usercode 4 4 ,deptcode 5 5 ,deptname 6 6 ,targetcode 7 7 ,targetname 8 8 ,sum(coalesce(day_value,0)) day_value 9 9 ,sum(coalesce(mon_value,0)) mon_value 1010 ,sum(coalesce(year_value,0)) year_value 1111 from( 1212 select datatime,usercode,deptcode,deptname,targetcode,targetname,day_value,mon_value,year_value 1313 from MOVEBI.T_GMS_MBI_TARGET_DATA 1414 pivot(max(targetvalue) for type in (1 day_value,2 mon_value,3 year_value)) where targetcode = '31227061') 1515 group by usercode 1616 ,deptcode 1717 ,deptname 1818 ,targetcode 1919 ,targetname; 2020 --方法二：对原始表处理 2121 select * 2222 from (select '20181017' datatime, 2323 usercode, 2424 deptcode, 2525 deptname, 2626 targetcode, 2727 targetname, 2828 targetvalue, 2929 type 3030 from MOVEBI.T_GMS_MBI_TARGET_DATA 3131 where datatime in ('20181017', '201810') 3232 and targetcode = '31227061') t 3333 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语句会很长，而且执行速度效率也没有前者高
1select student,'语文' 科目, (select 语文 from tmp_2 where student=f.student) 成绩 from tmp_2 f 2union 3select student,'数学' 科目, (select 数学 from tmp_2 where student=f.student) 成绩 from tmp_2 f 4union 5select student,'英语' 科目, (select 英语 from tmp_2 where student=f.student) 成绩 from tmp_2 f 6union 7select student,'物理' 科目, (select 物理 from tmp_2 where student=f.student) 成绩 from tmp_2 f 8------------------------------------------- 9select student,'语文' 科目,语文 from tmp_2 10union 11select student,'数学' 科目,语文 from tmp_2 12union 13select student,'英语' 科目,语文 from tmp_2 14union 15select student,'物理' 科目,语文 from tmp_2
（注：此为学习记录笔记，仅供参考若有问题请指正，后续补充......）
参考文档：https://blog.csdn.net/xiaokui\_wingfly/article/details/42419207