Declare @S Varchar(8000)
Select @S = 'Select realname'
Select @S = @S + ', sum(Case CourseName When cousername=''' + rtrim(CourseName) + ''' Then score Else 0.00 End) As '''+ rtrim(CourseName)+''''
From SudentScoreTB where classid=94 Group By coursename
Select @S = @S + ' From SudentScoreTB Group By realname'
EXEC(@S)
GO
Select @S = 'Select realname'
Select @S = @S + ', sum(Case CourseName When cousername=''' + rtrim(CourseName) + ''' Then score Else 0.00 End) As '''+ rtrim(CourseName)+''''
From SudentScoreTB where classid=94 Group By coursename
Select @S = @S + ' From SudentScoreTB Group By realname'
EXEC(@S)
GO
Select @S = 'Select realname'
Select @S = @S + ', sum(Case CourseName + ClassID When ''' + CourseName + ClassID + ''' Then score Else 0.00 End) As ['+ CourseName + ClassID + ']'
From SudentScoreTB where classid=94 Group By coursename
Select @S = @S + ' From SudentScoreTB Group By realname'
EXEC(@S)