sql 查询

[隨遇灬而安]

学生表 Student：
StudentID StudentName
1 张
2 李

课程表 Course：
CourseID CourseName
1 语文
2 数学
3 英语

成绩表 Scores：
CourseID StudentID score
1 1 78
2 1 79
3 1 80
1 2 81
2 2 82
3 2 83

通过sql得到如下表：
学生姓名 语文 数学 英语
张 78 79 80
李 81 82 83

求前辈指点，这个sql该怎么写

[吉普赛的歌]

USE tempdb
GO
IF OBJECT_ID('Student') IS NOT NULL DROP TABLE Student
IF OBJECT_ID('Course') IS NOT NULL DROP TABLE Course
IF OBJECT_ID('Scores') IS NOT NULL DROP TABLE Scores
GO
CREATE TABLE Student(StudentID INT PRIMARY KEY,StudentName NVARCHAR(10))
CREATE TABLE Course(CourseID INT,CourseName NVARCHAR(10))
CREATE TABLE Scores(CourseID INT,StudentID INT,score INT)
GO
SET NOCOUNT ON
INSERT INTO Student
SELECT  1,N'张'
union ALL SELECT 2,N'李'

INSERT INTO Course
SELECT 1,N'语文'
union ALL SELECT 2,N'数学'
union ALL SELECT 3,N'英语'

INSERT INTO Scores
SELECT 1,1,78
union ALL SELECT 2,1,79
union ALL SELECT 3,1,80
union ALL SELECT 1,2,81
union ALL SELECT 2,2,82
union ALL SELECT 3,2,83

SELECT 
StudentName  AS [姓名]
,MAX(CASE WHEN c.CourseName=N'语文' THEN s.score ELSE 0 END) AS [语文]
,MAX(CASE WHEN c.CourseName=N'数学' THEN s.score ELSE 0 END) AS [数学]
,MAX(CASE WHEN c.CourseName=N'英语' THEN s.score ELSE 0 END) AS [英语]
FROM Student AS stu LEFT JOIN Scores AS s ON stu.StudentID=s.StudentID 
	LEFT JOIN Course AS c ON s.CourseID=c.CourseID
GROUP BY stu.StudentName,stu.StudentID

[隨遇灬而安]

谢谢你的解答 请问一下有没有一行查询就能达到这个效果的sql（非存储过程），我的实际需求更复杂，我只是想借鉴这个例子里面的精华——查询表，将结果作为下一个查询的列名。就是这个例子中的 课程表里的语文、数学、英语 作为了查询结果里的列名。

[二月十六]

如果比较复杂需要用动态执行的方式，楼主可以百度下行转列关键字

[二月十六]

楼主可以看看这篇博客，有问题再问。https://blog.csdn.net/sinat_28984567/article/details/79865046

[吉普赛的歌]

--测试数据按我 #1 的
------------ 动态获取列名, 行列转置 ---------------
DECLARE @sql NVARCHAR(MAX)
SET @sql='
SELECT 
StudentName  AS [姓名]
'+
(
SELECT 
',MAX(CASE WHEN c.CourseName=N'''+c.CourseName+''' THEN s.score ELSE 0 END) AS ['+c.CourseName+']'
FROM dbo.Course AS c
FOR XML PATH('')
)
+
'FROM Student AS stu LEFT JOIN Scores AS s ON stu.StudentID=s.StudentID 
	LEFT JOIN Course AS c ON s.CourseID=c.CourseID
GROUP BY stu.StudentName,stu.StudentID'
PRINT @sql
EXEC (@sql)

不知道有多少科， 只想动态生成列， 没什么更好的办法， 只能用动态SQL。

[二月十六]

DECLARE @sql VARCHAR(MAX)
SET @sql = ';WITH cte AS (
SELECT
    StudentName,
    CourseName,
    score
FROM
    dbo.Scores
    JOIN
        dbo.Student
            ON Student.StudentID = Scores.StudentID
    JOIN
        dbo.Course
            ON Course.CourseID = Scores.CourseID
)
select StudentName'
;WITH cte AS (
SELECT
    StudentName,
    CourseName,
    score
FROM
    dbo.Scores
    JOIN
        dbo.Student
            ON Student.StudentID = Scores.StudentID
    JOIN
        dbo.Course
            ON Course.CourseID = Scores.CourseID
)
SELECT  @sql = @sql + ',max(case CourseName when ''' + CourseName
        + ''' then Score else 0 end)[' + CourseName + ']'
FROM    ( SELECT DISTINCT
                    cte.CourseName
          FROM      cte
        ) a
SET @sql = @sql
    + 'from cte group by StudentName'
EXEC(@sql)