交叉查询

SELECT student_key,

       SUM(CASE course_key WHEN 1 THEN mark else 0 end) AS 'English',

       SUM(CASE course_key WHEN 2 THEN mark else 0 end) AS 'Maths',

       SUM(CASE course_key WHEN 3 THEN mark else 0 end) AS 'Physics'

FROM   Score

GROUP  BY student_key

 

DECLARE @sql VARCHAR(1024)

SET    @sql = ''

SELECT @sql = @sql + 'SUM(CASE course_key WHEN ' + CAST(course_key AS VARCHAR)

       + ' THEN mark else 0 end)' + ','

FROM   (SELECT DISTINCT course_key FROM Score) AS obj

SELECT @sql = LEFT(@sql, LEN(@sql) - 1)

SET    @sql = 'SELECT student_key,' + @sql + ' FROM Score GROUP BY student_key'

--SELECT   @sql

EXECUTE (@sql)