PIVOT、UNPIVOT 转换行与列

可以使用 PIVOT 和 UNPIVOT 运算符将表值表达式更改为另一个表。

• PIVOT 是将表达式某一列中的唯一值转换为输出中的多个列，并在必要时对最终输出中所需的其余任何列值运行聚合。
• 与 PIVOT 执行的操作相反，UNPIVOT 将表值表达式的列轮换为列值。

---

语法

SELECT <non-pivoted column>,  
    [first pivoted column] AS <column name>,  
    [second pivoted column] AS <column name>,  
    ...  
    [last pivoted column] AS <column name>  
FROM  
    (<SELECT query that produces the data>)   AS <alias for the source query>  
PIVOT  
(  
    <aggregation function>(<column being aggregated>)  
FOR   
[<column that contains the values that will become column headers>]   
    IN ( [first pivoted column], [second pivoted column],  
    ... [last pivoted column])  
) AS <alias for the pivot table>  
<optional ORDER BY clause>;  

示例：PIVOT

基础数据准备

create table StudentScores
(
class varchar(10),               --班级
username    varchar(20),         --姓名
[subject]    varchar(30),        --科目
score        float,              --成绩
);
go
-- 01、插入测试数据
insert into StudentScores(class,username,[subject],score) values('一班','张三', '语文', 80);
insert into StudentScores(class,username,[subject],score) values('一班','张三', '数学', 90);
insert into StudentScores(class,username,[subject],score) values('一班','张三', '英语', 70);
insert into StudentScores(class,username,[subject],score) values('一班','张三', '生物', 85);
insert into StudentScores(class,username,[subject],score) values('一班','李四', '语文', 70);
insert into StudentScores(class,username,[subject],score) values('一班','李四', '数学', 92);
insert into StudentScores(class,username,[subject],score) values('一班','李四', '英语', 76);
insert into StudentScores(class,username,[subject],score) values('一班','王二', '语文', 60);
insert into StudentScores(class,username,[subject],score) values('一班','王二', '数学', 82);
insert into StudentScores(class,username,[subject],score) values('一班','王二', '英语', 96);
insert into StudentScores(class,username,[subject],score) values('一班','王二', '生物', 78);

insert into StudentScores(class,username,[subject],score) values('二班','吴六', '语文', 90);
insert into StudentScores(class,username,[subject],score) values('二班','吴六', '数学', 94);
insert into StudentScores(class,username,[subject],score) values('二班','吴六', '英语', 78);
insert into StudentScores(class,username,[subject],score) values('二班','吴六', '生物', 75);
insert into StudentScores(class,username,[subject],score) values('二班','郑五', '语文', 78);
insert into StudentScores(class,username,[subject],score) values('二班','郑五', '数学', 94);
insert into StudentScores(class,username,[subject],score) values('二班','郑五', '英语', 86);
insert into StudentScores(class,username,[subject],score) values('二班','郑五', '英语', 86);
insert into StudentScores(class,username,[subject],score) values('二班','张三', '语文', 70);
insert into StudentScores(class,username,[subject],score) values('二班','张三', '数学', 88);
insert into StudentScores(class,username,[subject],score) values('二班','张三', '英语', 90);
insert into StudentScores(class,username,[subject],score) values('二班','张三', '生物', 88);
-- 02、查询数据
select * from StudentScores;

基础数据

问题一

如果我想知道每位学生的每科成绩，而且每个学生的全部成绩排成一行

--A1、传统 case 方法
select class as '班级',username as '姓名'
,max(case [subject] when '语文' then score else 0 end) as '语文'
,max(case [subject] when '数学' then score else 0 end) as '数学'
,max(case [subject] when '英语' then score else 0 end) as '英语'
,max(case [subject] when '生物' then score else 0 end) as '生物'
from StudentScores
group by class,username
order by class desc,username desc;

-- A2：PIVOT 方法
select class as '班级',username as '姓名',[语文],[数学],[英语],[生物] 
from StudentScores a
PIVOT
(
  max(a.score) 
  for a.subject in([语文],[数学],[英语],[生物])
) b;

问题一结果

问题二

如果我想知道每个班级每门课的平均成绩（排除“姓名”聚合项）
错误示范：结果并没有在class层级聚合，依然在全部未拆分层级聚合

select class as '班级',[语文],[数学],[英语],[生物] 
from StudentScores a
PIVOT
(
    avg(a.score) 
    for a.subject in([语文],[数学],[英语],[生物])
)b;

错误示范

正确示范：首先确保数据源只保留需聚合的字段，即先在from语句中进行一次聚合

--A1、传统 case 方法
select class as '姓名'
,sum(case [subject] when '语文' then score else 0 end)/sum(case [subject] when '语文' then 1 else 0 end) as '语文'
,sum(case [subject] when '数学' then score else 0 end)/sum(case [subject] when '数学' then 1 else 0 end) as '数学'
,sum(case [subject] when '英语' then score else 0 end)/sum(case [subject] when '英语' then 1 else 0 end) as '英语'
,sum(case [subject] when '生物' then score else 0 end)/sum(case [subject] when '生物' then 1 else 0 end) as '生物'
from StudentScores
group by class;

-- A2：PIVOT 方法
select class,[语文],[数学],[英语],[生物] 
from (select class,subject,avg(score) as score 
        from StudentScores 
        group by class,subject) a
PIVOT
(
    max(a.score) 
    for a.subject in([语文],[数学],[英语],[生物])
)b;

正确示范

示例：UNPIVOT

--插入数据
select class,[语文],[数学],[英语],[生物] 
into #1
from (select class,subject,avg(score) as score from StudentScores group by class,subject) a
PIVOT
(
    max(a.score) 
    for a.subject in([语文],[数学],[英语],[生物])
) b 

select * from #1

基础数据

课程拆分到列：

select b.class,b.subject,b.score 
from #1 a
UNPIVOT
(
    score for subject in([语文],[数学],[英语],[生物])
)b;

查询结果

请注意，UNPIVOT 并不完全是 PIVOT 的逆操作。

• PIVOT 执行聚合，并将多个可能的行合并为输出中的一行。
• UNPIVOT 不重现原始表值表达式的结果，因为行已被合并。
  另外，UNPIVOT 输入中的 NULL 值也在输出中消失了。 如果值消失，表明在执行 PIVOT 操作前，输入中可能就已存在原始 NULL 值。

【参考】
MS:FROM - 使用 PIVOT 和 UNPIVOT
SQL Server：使用 PIVOT 行转列和 UNPIVOT 列转行