SqlServer的Pivot和Unpivot用法

pivot 运算符使用
首先我们创建测试表，并且插入相应的数据

create table pivotDemo(
id int not null,
subjects char(20) not null,
score int not null
)

 

 

insert into pivotDemo
values(1,'数学',50),
(1,'语文',30),
(1,'英语',53),
(2,'数学',40),
(2,'语文',70),
(2,'英语',93),
(3,'数学',56),
(3,'语文',39),
(3,'英语',57)

 

插入数据如下

id    subjects    score
1    数学 50
1    语文 30
1    英语 53
2    数学 40
2    语文 70
2    英语 93
3    数学 56
3    语文 39
3    英语 57

 

现在的数据有三列，分别是学号，学科，分数
我们想要的表格式是 学号，数学，语文，英语 每一行数据是该生在相应科目的分数
首先我们可以用case when 方式来实现，具体实现如下

select id ,
case subjects when '数学' then score end as math,
case subjects when '语文' then score end as chinese,
case subjects when '英语' then score end as english
from pivotDemo

 

上述查询可以得到的结果是

id    math    chinese    english
1    50    NULL    NULL
1    NULL    30    NULL
1    NULL    NULL    53
2    40    NULL    NULL
2    NULL    70    NULL
2    NULL    NULL    93
3    56    NULL    NULL
3    NULL    39    NULL
3    NULL    NULL    57

 

我们使用聚合函数将数据进行合并

select id ,
max(case subjects when '数学' then score end) as math,
max(case subjects when '语文' then score end) as chinese,
max(case subjects when '英语' then score end) as english
from pivotDemo
group by id

 

上述查询得到的结果是

1 2 3 4

id  math    chinese english 1   50  30  53 2   40  70  93 3   56  39  57

　　

按照上面的想法，我们将格式带入pivot运算符

select id,math,chinese,english from pivotDemo pivot(sum(score) for [subjects] in ([math],[chinese],[english])) as p

 

得到结果集如下

id    math    chinese    english
1    NULL    NULL    NULL
2    NULL    NULL    NULL
3    NULL    NULL    NULL

 

之所以全为NULL，是因为 IN 表达式里面的元素必须是原表中的数据，在该查询中，表达式应如下所示

select id,数学 as math,语文 as chinese,英语 as english from pivotDemo pivot(max(score) for subjects in ([数学],[语文],[英语])) as p

 

上述查询得到的结果如下

id    math    chinese    english
1    50    30    53
2    40    70    93
3    56    39    57

 

pivot 的第一个输入是源表需要聚合的列，for关键字后是需要将值拆分成目标列字段名的源列名，in后面跟的是需要拆分出来的目标列（数据来源于for关键字跟着的源列的各种属性）

在这里例子中，我们使用的聚合函数是MAX（）提取了score中不为NULL的最大值，假如存在一个学生有多个语文成绩，我们可以使用AVG()获取语文成绩的平均值

unpivot 运算符使用

使用以下语句得到测试数据

select id,数学 as math,语文 as chinese,英语 as english into unpivotDemo from pivotDemo pivot(max(score) for subjects in ([数学],[语文],[英语])) as p

 

结果如下

select id,数学 as math,语文 as chinese,英语 as english into unpivotDemo from pivotDemo pivot(max(score) for subjects in ([数学],[语文],[英语])) as p

 

这次我们要做的是将上述数据装换成以下格式

id    subjects    score
1    数学 50
1    语文 30
1    英语 53
2    数学 40
2    语文 70
2    英语 93
3    数学 56
3    语文 39
3    英语 57

 

现在的数据有三列，分别是学号，数学，语文，英语三列
我们想要的表格式是 学号，科目，分数 每一行数据是该生在某一科目的科目名称和分数
首先我们可以用case when 方式来实现，具体实现如下

select id,subjects, 
case subjects when '语文' then chinese when '数学' then math when '英语' then english end as score
from unpivotDemo
cross join(
select '语文' as subjects 
union all 
select '数学' 
union all
select '英语')as subjects

 

或者

select id,subjects, 
case subjects when '语文' then chinese when '数学' then math when '英语' then english end as score
from unpivotDemo
cross join(values('语文'),('数学'),('英语')) as subjectsTable(subjects)

 

或者

select id,'math' as subjects,math as num from unpivotDemo union all
select id,'chinese' as subjects, chinese as num from unpivotDemo union all
select id,'english' as subjects, english as num from unpivotDemo

 

得到结果集如下

id    subjects    score
1    语文    30
1    数学    50
1    英语    53
2    语文    70
2    数学    40
2    英语    93
3    语文    39
3    数学    56
3    英语    57

 

SQLServer 提供的unpivot 运算符可以简化这一过程 ，使用unpivot运算符的写法如下

select id ,
case subjects when 'chinese' then '语文' when 'math' then '数学' when 'english' then '英语' end as score,
a.score
from unpivotDemo
unpivot(score for subjects in ([math],[chinese],[english])) as a

 

结果如下

id    subjects    score
1    数学    50
1    语文    30
1    英语    53
2    数学    40
2    语文    70
2    英语    93
3    数学    56
3    语文    39
3    英语    57

 

unpivot的第一个输入是用于保存源表列值得目标列名称，在这个例子中是需要保留 源表score列到目的表的score列，for关键字后面是需要保留的目标列名称，它的数据来源于源表的 math,chinese,english等列


整体看下来，这两个函数就是SQLServer帮我们封装好了一系列的case when 过程。与这两个运算符相比 case when 的写法更容易被理解，他们的写法会更简洁一些，各有利弊，这两个运算时只是写法简洁，性能并没有提升太多。