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