sql 语句列行转换问题，解决一下，

CREATE TABLE [dbo].[SudentScoreTB] (
[ID] [int] IDENTITY (1, 1) NOT NULL ,
[CourseID] [int] NOT NULL ,
[PoliceID] [varchar] (100) COLLATE Chinese_PRC_CI_AS NOT NULL ,
[Score] [decimal](18, 2) NOT NULL
) ON [PRIMARY]
GO

成绩表
ID PoliceID courseID score/成绩
111 60
212 70
32 1 65
422 73


结果
PoliceID score1 score2
1 60 70
2 65 73

用sql怎么写

上面三个表我已经有了，就是要得下面的结果，
Declare @S Varchar(8000)
Select @S = 'Select PoliceID '
Select @S = @S + ', SUM(Case CourseID When ' + CourseID + ' Then score Else 0.00 End) As score '+ CourseID
From SudentScoreTB Group By CourseID
Select @S = @S + ' From SudentScoreTB Group By PoliceID '
EXEC(@S)
GO

出错
服务器: 消息 245，级别 16，状态 1，行 3
将 varchar 值 ' Then score Else 0.00 End) As score ' 转换为数据类型为 int 的列时发生语法错误。



[解决办法]
Declare @S Varchar(8000)
Select @S = 'Select PoliceID '
Select @S = @S + ', SUM(Case CourseID When ' + rtrim(CourseID) + ' Then score Else 0.00 End) As score '+ rtrim(CourseID)
From SudentScoreTB Group By CourseID
Select @S = @S + ' From SudentScoreTB Group By PoliceID '
EXEC(@S)
GO