SQL行转列问题

浏览数：26 / 时间：2015年06月12日

一：前言
　　在我们做的一些项目中经常会碰到把行转化为列的问题，这里进行总结一下：将下图一的格式转换为图二的格式。

图一

图二

二：首先向数据库插入数据。

IF OBJECT_ID(‘Tmark‘) IS NOT NULL DROP TABLE Tmark  /*判断数据库中是否存在Tmark表格*/

go

CREATE TABLE Tmark(Name VARCHAR(10),Course VARCHAR(10),Score INT)

insert into Tmark VALUES (‘张三‘,‘语文‘,74)
insert into Tmark VALUES (‘张三‘,‘数学‘,83)
insert into Tmark VALUES (‘张三‘,‘物理‘,93)
insert into Tmark VALUES (‘李四‘,‘语文‘,74)
insert into Tmark VALUES (‘李四‘,‘数学‘,84)
insert into Tmark VALUES (‘李四‘,‘物理‘,94)
insert into Tmark VALUES (‘王五‘,‘语文‘,86)
insert into Tmark VALUES (‘王五‘,‘数学‘,NUll)
insert into Tmark VALUES (‘王五‘,‘物理‘,NUll)

go

在数据库中有一个系统表sysobjects，里面存储了数据库各个对象的信息。可以查询下看看结果。可以看出每个对象都有一个ID，这个表存储了表，存储过程，触发器，视图等相关信息。

object_id就是根据对象名称返回该对象的id.
object_name是根据对象id返回对象名称.

select object_id(对象名)等同于:
select id from sysobjects where name=对象名

select object_name(id号)等同于:
select name from sysobjects where id=id号

SQL SERVER 2000以上版本都支持这个函数。

三：先从简单开始，由分组获得Name列

select Name from Tmark group by Name。

技术分享

四：增加一列语文

现在我们想在这个结果集中再添加1列，多了我们不加，因为你不论是能处理语文，还是数学，还是物理列，

那么其他的列只要原样照抄就可以了，我们就只在现在的基础上添加一个语文列。

SELECT Name, CASE WHEN course = ‘语文‘ THEN score END FROM Tmark GROUP BY Name

技术分享

我们看错误提示，Course 和Score 列要在聚合函数或者GROUP BY 子句中，那么我们先把Course 和Score 列放在GROUP BY 子句中

SELECT Name, CASE WHEN course = ‘语文‘ THEN score END FROM Tmark GROUP BY Name,Course ,Score

技术分享

从结果看，数据倒是有了，可是行多了点，看来只能从聚合函数入手(聚合函数中的列可以不出现在Group By之后)

五：从聚合函数添加列

select Name, SUM(CASE WHEN Course = ‘语文‘ THEN Score END) AS 语文
from Tmark Group by Name

技术分享

对于其他的列就可以直接复制，粘贴了。

SELECT Name,
SUM(CASE WHEN Course = ‘语文‘ THEN Score END) AS 语文,
SUM(CASE WHEN Course = ‘数学‘ THEN Score END) AS 数学,
SUM(CASE WHEN Course = ‘物理‘ THEN Score END) AS 物理
FROM Tmark GROUP BY Name

技术分享

六：对Null的处理

对于没参加考试的同学，那么我们不应该显示‘NULL‘，应该显示为‘0‘，可是结果有NULL，那不太好，我们来把CASE WHEN THEN END写完，在此之间加个ELSE 0

1 SELECT Name,
2 SUM(CASE WHEN Course = ‘语文‘ THEN Score ELSE 0 END) AS 语文,
3 SUM(CASE WHEN Course = ‘数学‘ THEN Score ELSE 0 END) AS 数学,
4 SUM(CASE WHEN Course = ‘物理‘ THEN Score ELSE 0 END) AS 物理
5 FROM Tmark GROUP BY Name
6 
7 go