一、行转列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
