有两种方法可以执行
PIVOT对值进行硬编码的静态方法和执行时确定列的动态方法。
即使您想要一个动态版本，有时也更容易从静态版本开始，
PIVOT然后朝着动态版本迈进。
静态版本：
SELECt studentid, name, sex,[C], [C++], [English], [Database], [Math], total, averagefrom ( select s1.studentid, name, sex, subjectname, score, total, average from Score s1 inner join ( select studentid, sum(score) total, avg(score) average from score group by studentid ) s2 on s1.studentid = s2.studentid) xpivot ( min(score) for subjectname in ([C], [C++], [English], [Database], [Math])) p
参见带有演示的SQL Fiddle
现在，如果您不知道将要转换的值，则可以为此使用Dynamic SQL：
DECLARE @cols AS NVARCHAr(MAX), @query AS NVARCHAr(MAX)select @cols = STUFF((SELECT distinct ',' + QUOTENAME(SubjectName) from Score FOR XML PATH(''), TYPE ).value('.', 'NVARCHAr(MAX)') ,1,1,'')set @query = 'SELECt studentid, name, sex,' + @cols + ', total, average from ( select s1.studentid, name, sex, subjectname, score, total, average from Score s1 inner join ( select studentid, sum(score) total, avg(score) average from score group by studentid ) s2 on s1.studentid = s2.studentid ) x pivot ( min(score) for subjectname in (' + @cols + ') ) p 'execute(@query)参见带有演示的SQL Fiddle
两种版本将产生相同的结果。
只是为了完善答案，如果您没有
PIVOT函数，则可以使用
CASE和聚合函数获得此结果：
select s1.studentid, name, sex, min(case when subjectname = 'C' then score end) C, min(case when subjectname = 'C++' then score end) [C++], min(case when subjectname = 'English' then score end) English, min(case when subjectname = 'Database' then score end) [Database], min(case when subjectname = 'Math' then score end) Math, total, averagefrom Score s1inner join( select studentid, sum(score) total, avg(score) average from score group by studentid) s2 on s1.studentid = s2.studentidgroup by s1.studentid, name, sex, total, average
参见带有演示的SQL Fiddle
