在平常的工作中或者面试中，我们可能有遇到过数据库的纵横表的转换问题。今天我们就来讨论下。
1.创建表
首先我们来创建一张表。
sql语句:
1
--
1. 创建数据表
2
if
OBJECT_ID
(
'
Score
'
)
is
not
null
drop
table
Score
3
4
create
table
Score
5
(
6
姓名
nvarchar
(
128
),
7
课程
nvarchar
(
128
),
8
分数
int
9
)
10
11
insert
into
Score
values
(
'
张三
'
,
'
语文
'
,
98
)
12
insert
into
Score
values
(
'
张三
'
,
'
数学
'
,
89
)
13
insert
into
Score
values
(
'
张三
'
,
'
物理
'
,
78
)
14
insert
into
Score
values
(
'
李四
'
,
'
语文
'
,
79
)
15
insert
into
Score
values
(
'
李四
'
,
'
数学
'
,
88
)
16
insert
into
Score
values
(
'
李四
'
,
'
物理
'
,
100
)
17
18
select
*
from
Score
执行结果：
2. 传统的纵横表转换
2.1 纵表转横表
先看看我们要转成的横表张什么样子:
既然这个表只有两列，那么可以根据姓名进行分组。先把姓名拼凑出来，后面的分数我们再想办法。
sql:
select
t.姓名
2
from
Score
as
t
3
group
by
t.姓名
结果：
分析：
- 我们先拿到语文这个科目的分数。既然我们用到了group by 语句，这里肯定要用聚合函数来求分数。
- 而且我们只需要语文这一科的成绩，分组出来的 一共有 3列 ，分别是 语文、数学、物理 。 那么就需要判断科目来取分数。
这里符合我们需求的 case 语句就登场了。他和c#中switch-case 作用一样。
sql case 语句语法：
case
字段
when
值1
then
结果
when
值2
then
结果2
...
else
默认结果
end
求语文的分数就简单了：
select
t.姓名,
SUM
(
case
t.课程
when
'
语文
'
then
t.分数
else
0
end
)
as
语文
from
Score
as
t
group
by
t.姓名
结果：
既然语文的分数取到了，其他科目改变下条件就可以了。
完整的sql:
select
t.姓名,
SUM
(
case
t.课程
when
'
语文
'
then
t.分数
else
0
end
)
as
语文,
SUM
(
case
t.课程
when
'
数学
'
then
t.分数
else
0
end
)
as
数学,
SUM
(
case
t.课程
when
'
物理
'
then
t.分数
else
0
end
)
as
物理
from
Score
as
t
group
by
t.姓名
OK,到这儿，我们传统方式的纵表转横表就大功告成了。
2.2 横表转纵表
那么我们可以把转换过来的横表再转换回去吗？
我们先把刚刚转好的表，插入一个新表ScoreHb 中。
1
--
转换的表插入新表
2
select
t.姓名,
3
SUM
(
case
t.课程
when
'
语文
'
then
t.分数
else
0
end
)
as
语文,
4
SUM
(
case
t.课程
when
'
数学
'
then
t.分数
else
0
end
)
as
数学,
5
SUM
(
case
t.课程
when
'
物理
'
then
t.分数
else
0
end
)
as
物理
6
into
ScoreHb
7
from
Score
as
t
8
group
by
t.姓名
这时ScoreHb 就是我们刚转换好的横表，我们再想办法把他转回来。
怎么转呢？ 一步步来。我们也先把张三和李四的语文成绩查出来。
sql:
1
--
张三李四语文的分数
2
select
t.姓名,
3
'
语文
'
as
课程,
4
t.语文
as
分数
5
from
ScoreHb
as
t
结果：
还有两科的数据怎么办呢？ 很简单，我们一个个都查出来，然后用 union all 把他们组合为一张表就可以了。
sql:
1
--
union all链接3个科目
2
select
t.姓名,
3
'
语文
'
as
课程,
4
t.语文
as
分数
5
from
ScoreHb
as
t
6
union
all
7
select
t.姓名,
8
'
数学
'
as
课程,
9
t.数学
as
分数
10
from
ScoreHb
as
t
11
union
all
12
select
t.姓名,
13
'
物理
'
as
课程,
14
t.物理
as
分数
15
from
ScoreHb
as
t
16
order
by
t.姓名 desc
结果：
这样，我们就把表又变回去了。
但是大家有没有觉得很麻烦呢？别急，我们有更简单的办法。下面为大家介绍pivot关系运算符。
3. 用pivot和unpivot运算符进行转换
pivot是sql server 2005 提供的运算符，所以只要数据库在05版本以上的都可以使用。主要用于行和列的转换。
3.1 pivot纵表转横表
sql:
1
select
2
t2.姓名,
3
t2.数学,
4
t2.物理,
5
t2.语文
6
from
Score
as
t1
7
pivot (
sum
(分数)
for
课程
in
(数学,语文,物理))
as
t2
结果：
是不是代码简洁多了。
pivot将原来表中 课程字段中的 数据行 数学,语文,物理 转换为列，并用sum取对应列的值。
我们只需要记住它的用法就可以了。
3.2 unpivot 横表转纵表
既然有privot可以纵表转横表。那么有没有运算符帮我们转回来呢？
答案是肯定的，他就是unpivot
sql:
1
select
2
*
3
from
4
ScoreHb
5
unpivot (分数
for
课程
in
(语文,数学,物理))
as
t4
结果：
unpivot 将 语文,数学,物理 列转为行，分数为新的一列存放对应的值。
是不是比我们之前一个个表查询拼接，方便了很多。
