sql server多行数据合并显示

现有三个数据表,分别是学生表,课程表,成绩表。它们的结构与样例数据如下：

学生表：
学生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查询分析器里运行

--方法一
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 成绩表