Sql Server里巧用Case将多行显示的数据合并为一行显示

转载地址：http://www.cnblogs.com/kingthy/archive/2008/04/29/1175697.html

昨晚在CSDN论坛上看到有某个人问了类似这样的一个问题，现有三个数据表,分别是学生表,课程表,成绩表。它们的结构与样例数据如下：

学生表：
学生Id    姓名
1             张三
2             李四
3             王五


课程表：
课程Id    课程名
1             语文
2             化学
3             外语
4             物理


成绩表：
学生Id     课程Id        成绩
1               1                 60
1               2                 70
1               3                 65
1               4                 90
2               1                 80
2               2                 65
2               3                 85
2               4                 80
3               1                 50
3               2                 75
3               3                 85
3               4                 60

现要求在一行中输出每个学生的所有课程的成绩单，显示样例格式如下：
姓名        语文        化学       外语        物理
张三        60            70           65            90
李四        80            65           85            80
王五        50            75           85            60

当大家看到这样的题目时会优先考虑到的是怎样的一条SQL语句呢？嵌套Select？对，在将行转换为列时，也许这种方法是最优先考虑到（或者你是高手，所以不是优先考虑到这个而是其它)，所以我最开始也写出了下面这条语句：

SELECT  B.姓名,
( SELECT  成绩  FROM  成绩表  INNER   JOIN  课程  ON  成绩表.课程ID = 课程.课程ID  WHERE  成绩表.学生ID = B.学生ID  AND  课程.课程名 = ' 语文 '  )  AS  语文,
( SELECT  成绩  FROM  成绩表  INNER   JOIN  课程  ON  成绩表.课程ID = 课程.课程ID  WHERE  成绩表.学生ID = B.学生ID  AND  课程.课程名 = ' 化学 '  )  AS  化学,
( SELECT  成绩  FROM  成绩表  INNER   JOIN  课程  ON  成绩表.课程ID = 课程.课程ID  WHERE  成绩表.学生ID = B.学生ID  AND  课程.课程名 = ' 外语 '  )  AS  外语,
( SELECT  成绩  FROM  成绩表  INNER   JOIN  课程  ON  成绩表.课程ID = 课程.课程ID  WHERE  成绩表.学生ID = B.学生ID  AND  课程.课程名 = ' 物理 '  )  AS  物理
FROM  学生 B

这样我们的目的是达到了，但后来我又想了一下，因为我们要的数据其实都在 成绩表 里，只不过现有的是用行来存放，那我们怎么将它转换为列显示呢？嗯，这也许就要搬出聚合函数加Case条件来处理了！最终的SQL语句如下：

SELECT  姓名,
MAX ( CASE  课程名  WHEN   ' 语文 '   THEN  成绩  ELSE   0   END )  AS  语文,
MAX ( CASE  课程名  WHEN   ' 化学 '   THEN  成绩  ELSE   0   END )  AS  化学,
MAX ( CASE  课程名  WHEN   ' 外语 '   THEN  成绩  ELSE   0   END )  AS  外语,
MAX ( CASE  课程名  WHEN   ' 物理 '   THEN  成绩  ELSE   0   END )  AS  物理
FROM  ( SELECT  B.姓名,C.课程名,D.成绩  FROM  成绩表 D 
INNER   JOIN  学生 B  ON  B.学生ID = D.学生ID 
INNER   JOIN  课程 C  ON  C.课程ID = D.课程ID)  AS  TMP  GROUP   BY  姓名

运行后，也是可以得到正确的数据，下面给出测试代码，大家可以直接在SQL查询分析器里运行

CREATE   TABLE  学生 (学生ID  INT , 姓名  VARCHAR ( 20 ))
CREATE   TABLE  课程 (课程ID  INT , 课程名  VARCHAR ( 20 ))
CREATE   TABLE  成绩表 (学生ID  INT , 课程ID  INT , 成绩  INT )

INSERT   INTO  学生
SELECT   1 , ' 张三 '   UNION   ALL
SELECT   2 , ' 李四 '   UNION   ALL
SELECT   3 , ' 王五 '

INSERT   INTO  课程
SELECT   1 , ' 语文 '   UNION   ALL
SELECT   2 , ' 化学 '   UNION   ALL
SELECT   3 , ' 外语 '   UNION   ALL
SELECT   4 , ' 物理 '

INSERT   INTO  成绩表
SELECT   1 , 1 , 60   UNION   ALL
SELECT   1 , 2 , 70   UNION   ALL
SELECT   1 , 3 , 65   UNION   ALL
SELECT   1 , 4 , 90   UNION   ALL
SELECT   2 , 1 , 80   UNION   ALL
SELECT   2 , 2 , 65   UNION   ALL
SELECT   2 , 3 , 85   UNION   ALL
SELECT   2 , 4 , 80   UNION   ALL
SELECT   3 , 1 , 50   UNION   ALL
SELECT   3 , 2 , 75   UNION   ALL
SELECT   3 , 3 , 85   UNION   ALL
SELECT   3 , 4 , 60

-- 方法一
SELECT  B.姓名,
( SELECT  成绩  FROM  成绩表  INNER   JOIN  课程  ON  成绩表.课程ID = 课程.课程ID  WHERE  成绩表.学生ID = B.学生ID  AND  课程.课程名 = ' 语文 '  )  AS  语文,
( SELECT  成绩  FROM  成绩表  INNER   JOIN  课程  ON  成绩表.课程ID = 课程.课程ID  WHERE  成绩表.学生ID = B.学生ID  AND  课程.课程名 = ' 化学 '  )  AS  化学,
( SELECT  成绩  FROM  成绩表  INNER   JOIN  课程  ON  成绩表.课程ID = 课程.课程ID  WHERE  成绩表.学生ID = B.学生ID  AND  课程.课程名 = ' 外语 '  )  AS  外语,
( SELECT  成绩  FROM  成绩表  INNER   JOIN  课程  ON  成绩表.课程ID = 课程.课程ID  WHERE  成绩表.学生ID = B.学生ID  AND  课程.课程名 = ' 物理 '  )  AS  物理
FROM  学生 B

-- 方法二
SELECT  姓名,
MAX ( CASE  课程名  WHEN   ' 语文 '   THEN  成绩  ELSE   0   END )  AS  语文,
MAX ( CASE  课程名  WHEN   ' 化学 '   THEN  成绩  ELSE   0   END )  AS  化学,
MAX ( CASE  课程名  WHEN   ' 外语 '   THEN  成绩  ELSE   0   END )  AS  外语,
MAX ( CASE  课程名  WHEN   ' 物理 '   THEN  成绩  ELSE   0   END )  AS  物理
FROM  ( SELECT  B.姓名,C.课程名,D.成绩  FROM  成绩表 D 
INNER   JOIN  学生 B  ON  B.学生ID = D.学生ID 
INNER   JOIN  课程 C  ON  C.课程ID = D.课程ID)  AS  TMP  GROUP   BY  姓名


DROP   TABLE  学生
DROP   TABLE  课程
DROP   TABLE  成绩表

PS：用嵌套SELECT与用聚合函数加Case两者的效率如何，我没有测试，各位有兴趣的可测试一下