动态获取列名，行列转置

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

动态：

------------ 动态获取列名, 行列转置 ---------------
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)

---

单表的行列转置：

USE tempdb
GO
IF OBJECT_ID('dbo.[t]') IS NOT NULL 
	DROP TABLE dbo.[t]
GO
CREATE TABLE dbo.[t](
[Name] NVARCHAR(20)
,[DateId] INT
,Result	INT
)
GO
SET NOCOUNT ON
INSERT INTO dbo.[t] VALUES(N'15优先A1',20160125,1)
INSERT INTO dbo.[t] VALUES(N'15优先A1',20160225,2)
INSERT INTO dbo.[t] VALUES(N'15优先A2',20160125,1)
INSERT INTO dbo.[t] VALUES(N'15优先A2',20160225,2)
INSERT INTO dbo.[t] VALUES(N'15优先A3',20160125,1)
INSERT INTO dbo.[t] VALUES(N'15优先A3',20160225,2)
INSERT INTO dbo.[t] VALUES(N'15优先B',20160125,1)
INSERT INTO dbo.[t] VALUES(N'15优先B',20160225,2)
INSERT INTO dbo.[t] VALUES(N'15优先C',20160125,1)
INSERT INTO dbo.[t] VALUES(N'15优先C',20160225,2)

--静态写法（仅能用于Name 固定的情况）
SELECT DateId
,SUM(CASE WHEN Name='15优先A1' THEN Result ELSE 0 END) AS [15优先A1]
,SUM(CASE WHEN Name='15优先A2' THEN Result ELSE 0 END) AS [15优先A2]
,SUM(CASE WHEN Name='15优先A3' THEN Result ELSE 0 END) AS [15优先A3]
,SUM(CASE WHEN Name='15优先B' THEN Result ELSE 0 END) AS [15优先B]
,SUM(CASE WHEN Name='15优先C' THEN Result ELSE 0 END) AS [15优先B]
FROM t
GROUP BY [DateId]

--动态写法
DECLARE @sql NVARCHAR(MAX)
SET  @sql='SELECT DateId'
+(SELECT ',SUM(CASE WHEN Name='''+NAME+''' THEN Result ELSE 0 END) AS ['+t.Name+']' FROM t GROUP BY NAME FOR XML PATH(''))
+' FROM t
GROUP BY [DateId]
'
PRINT @sql
EXEC(@sql)