对于PIVOT和UNPIVOT函数的两个例子

对于PIVOT和UNPIVOT函数的两个例子

这几天工作中用到了PIVOT函数，总结了两个例子，以便以后进行查阅。
首先是一个关于PIVOT的例子，首先我们统计了两个班级的考试平均分，结果如下：
with temp as(
select ‘一班’ 班级,‘语文’ 科目,‘90’ 平均分 from dual union all
select ‘一班’ 班级,‘英语’ 科目,‘92’ 平均分 from dual union all
select ‘一班’ 班级,‘数学’ 科目,‘95’ 平均分 from dual union all
select ‘一班’ 班级,‘物理’ 科目,‘97’ 平均分 from dual union all
select ‘二班’ 班级,‘语文’ 科目,‘95’ 平均分 from dual union all
select ‘二班’ 班级,‘英语’ 科目,‘96’ 平均分 from dual union all
select ‘二班’ 班级,‘数学’ 科目,‘87’ 平均分 from dual union all
select ‘二班’ 班级,‘历史’ 科目,‘92’ 平均分 from dual)
select * from temp;

现在如果我们需要将同一班级的相应科目的平均分显示到同一行，传统方法我们会这么做：
1.先对班级进行分组（即GROUP BY 班级）
2.把对应的科目转换为相应的列名
3.把对应的列的平均分对应显示
with temp as(
select ‘一班’ 班级,‘语文’ 科目,‘90’ 平均分 from dual union all
select ‘一班’ 班级,‘英语’ 科目,‘92’ 平均分 from dual union all
select ‘一班’ 班级,‘数学’ 科目,‘95’ 平均分 from dual union all
select ‘一班’ 班级,‘物理’ 科目,‘97’ 平均分 from dual union all
select ‘二班’ 班级,‘语文’ 科目,‘95’ 平均分 from dual union all
select ‘二班’ 班级,‘英语’ 科目,‘96’ 平均分 from dual union all
select ‘二班’ 班级,‘数学’ 科目,‘87’ 平均分 from dual union all
select ‘二班’ 班级,‘历史’ 科目,‘92’ 平均分 from dual)
select 班级,
avg(decode(科目,‘语文’,平均分,’’)) as 语文,
avg(decode(科目,‘英语’,平均分,’’)) as 英语,
avg(decode(科目,‘数学’,平均分,’’)) as 数学,
avg(decode(科目,‘物理’,平均分,’’)) as 物理,
avg(decode(科目,‘历史’,平均分,’’)) as 历史
from temp group by 班级

下面使用PIVOT函数的方式进行查询：
with temp as(
select ‘一班’ 班级,‘语文’ 科目,‘90’ 平均分 from dual union all
select ‘一班’ 班级,‘英语’ 科目,‘92’ 平均分 from dual union all
select ‘一班’ 班级,‘数学’ 科目,‘95’ 平均分 from dual union all
select ‘一班’ 班级,‘物理’ 科目,‘97’ 平均分 from dual union all
select ‘二班’ 班级,‘语文’ 科目,‘95’ 平均分 from dual union all
select ‘二班’ 班级,‘英语’ 科目,‘96’ 平均分 from dual union all
select ‘二班’ 班级,‘数学’ 科目,‘87’ 平均分 from dual union all
select ‘二班’ 班级,‘历史’ 科目,‘92’ 平均分 from dual
)
select * from temp pivot(avg(平均分) for 科目 in (‘语文’,‘英语’,‘数学’,‘物理’,‘历史’))

可以看出PIVOT函数就是把需要的行转换为列，对未列出的列进行分组，对需要的值的列进行聚集运算。

然后是UNPIVOT函数，下面是刚查出来的一班的成绩：

此时如果我们需要获取一班的4科成绩里最高平均分是多少，因为max函数只能对列进行求值，所以此时需要对此数据进行行转列：

这样就可以对分数进行MAX操作了：
with temp as(
select ‘一班’ 班级,‘90’ 语文,‘92’ 英语,‘95’ 数学,‘97’ 物理 from dual)
select max(fenshu) from temp unpivot(fenshu for type in(语文,英语,数学,物理));

参考文章：https://www.cnblogs.com/xiaoganggis/p/4618108.html