declare @s nvarchar(4000)
Select     @s=isnull(@s+',','')+quotename([Course]) from Class group by[Course] --isnull(@s+',','') 去掉字符串@s中第一个逗号
exec('select [Student],'+@s+',[总成绩] from (select *,[总成绩]=sum([Score])over(partition by [Student]) from Class) a 
pivot (max([Score]) for [Course] in('+@s+'))b ')

--> 测试数据：[tb]
IF OBJECT_ID('[tb]') IS NOT NULL DROP TABLE [tb]
GO 
CREATE TABLE [tb]([PERA] VARCHAR(1),[PERB] VARCHAR(1),[D] INT,[S] INT)
INSERT [tb]
SELECT 'A','B',3,2 UNION ALL
SELECT 'A','C',5,4 UNION ALL
SELECT 'A','D',4,2
--------------开始查询--------------------------


DECLARE @s VARCHAR(MAX)
SET @s = ''
SELECT  @s = @s + ',[' + LTRIM([PERB]) + '_D]=max(case when [PERB]=' + QUOTENAME([PERB], '''') + ' then [D] else 0 end)'
FROM    [tb]
GROUP BY [PERB]

SELECT  @s = @s + ',[' + LTRIM([PERB]) + '_S]=max(case when [PERB]=' + QUOTENAME([PERB], '''') + ' then [S] else 0 end)'
FROM    [tb]
GROUP BY [PERB]

SET @s = 'select [PERA]' + @s + ' from [tb] group by [PERA]'
EXEC(@s)
/*
PERA B_D         C_D         D_D         B_S         C_S         D_S
---- ----------- ----------- ----------- ----------- ----------- -----------
A    3           5           4           2           4           2

(1 行受影响)

a
*/