有两种方法可以执行PIVOT静态(在其中对值进行硬编码)和动态(在其中执行时确定列).

There are two ways to perform a PIVOT static where you hard-code the values and dynamic where the columns are determined when you execute.

即使您想要一个动态版本，有时从静态PIVOT开始然后向动态版本过渡也更容易.

Even though you will want a dynamic version, sometimes it is easier to start with a static PIVOT and then work towards a dynamic one.

静态版本:

SELECT studentid, name, sex,[C], [C++], [English], [Database], [Math], 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 ([C], [C++], [English], [Database], [Math])
) p

请参见带有演示的SQL小提琴

现在，如果您不知道将要转换的值，则可以为此使用动态SQL:

Now, if you do not know the values that will be transformed then you can use Dynamic SQL for this:

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提琴

两个版本都会产生相同的结果.

Both versions will yield the same results.

只需四舍五入即可得到答案，如果您没有PIVOT函数，则可以使用CASE和聚合函数来获得以下结果:

Just to round out the answer, if you do not have a PIVOT function, then you can get this result using CASE and an aggregate function:

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, 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
group by s1.studentid, name, sex, total, average

请参见带有演示的SQL提琴