SQL Server行列转换_sqlserver 行转列

在使用SQL Server数据库的过程中我们经常会遇到需要将行数据和列数据相互转换显示的问题。对于这个问题SQL Server数据库有专门的内置函数PIVOT(行转列)、UNPIVOT(列转行)可以解决。下面我们就来分析一下这两个函数的使用方法。

一、行转列PIVOT

1、语法

select * from 源表名 as 表别名
pivot( 聚合函数(源表需要聚合显示的列的字段名)
for 源表数据中需要转换为列名的列的字段名 in (转换后的列名1,转换后的列名2…))
as 表别名

2、举个例子

先创建一个作为数据源的表插入少量数据：

create table StudentScores 
(
StudentName varchar(16),
Subject varchar(16),
Score  smallint
)

insert into StudentScores
values('张三','语文',85)
insert into StudentScores
values('张三','数学',90)
insert into StudentScores
values('张三','英语',86)
insert into StudentScores
values('李四','语文',92)
insert into StudentScores
values('李四','数学',87)
insert into StudentScores
values('李四','英语',90)
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19

此时 select * from StudentScores 直接查询结果如下：

下面是我们使用PIVOT函数转换后查询出的结果，先看下代码：

select * from StudentScores as s  --源数据
pivot(max(Score) for Subject in (语文,数学,英语)) as ss --转换后的结果数据
1
2

查询出的结果如图：

以上查询也可以使用动态查询语句来实现，具体方法请查看SQL Server纵表转横表

二、列转行UNPIVOT

1、语法

select * from 源表名 as 表别名
unpivot(定义由源表数据转换成的列的列名 for 定义由源表字段名转换成的列的列名 in
(源表字段名1,源表字段名2…)) as 表别名

2、实例

为求直观直接用上面实例的结果数据创建一个新表：

create table StudentGrades
(
StudentName varchar(16),
语文 smallint,
数学 smallint,
英语 smallint,
)
insert into StudentGrades
values('张三',85,90,86)
insert into StudentGrades
values('李四',92,87,90)
1
2
3
4
5
6
7
8
9
10
11

select * from StudentGrades直接查询结果如下：

使用UNPIVOT函数进行转换，代码如下：

select * from StudentGrades as sg --源数据
unpivot(Score for Subject in (语文,数学,英语)) as cjd --转换后的结果数据
1
2

结果如图：

和PIVOT函数的实例中源数据的表基本相同。当然我们也可以不创建新表直接使用以下代码查询出相同的结果：

select * from StudentScores as s 
pivot(max(Score) for Subject in (语文,数学,英语)) as ss
unpivot(Score for Subject in (语文,数学,英语)) as cjd
1
2
3

通过上面的例子，我们简单了解了PIVOT和UNPIVOT这两个函数的使用方法，在例子中我们用这两个函数实现了数据的行列逆转查询，虽然UNPIVOT函数可以将PIVOT函数转换后的结果还原成转换前的样子，但UNPIVOT 并不完全是 PIVOT 的逆操作。 PIVOT 执行聚合，并将多个可能的行合并为输出中的一行。 UNPIVOT 不重现原始表值表达式的结果，因为行已被合并。 另外，UNPIVOT 输入中的 NULL 值也在输出中消失了。 如果值消失，表明在执行 PIVOT 操作前，输入中可能就已存在原始 NULL 值。