If exists (select * From information_schema.tables where table_name = 'tb ')
Drop table TB
Go
Create Table Tb (name varchar (10), course varchar (10), score int, class varchar (10 ))
Insert into TB values ('zhang san', 'China', 74, 'class 1 ')
Insert into TB values ('zhang san', 'mat', 83, 'class 1 ')
Insert into TB values ('zhang san', 'Physical ', 93, '2 class ')
Insert into TB values ('Li si', 'China', 74, '2 class ')
Insert into TB values ('Li si', 'mat', 84, 'class 1 ')
Insert into TB values ('Li si', 'Physical ', 94, 'class 1 ')
Insert into TB values ('Li si', 'Physical ', 100, 'class 1 ')
Go
Select * from TB
Go -- sql2005
Declare @ SQL varchar (8000)
Select @ SQL = isnull (@ SQL + ',', '') + course from TB group by course
Exec ('select * from (select * from TB) a round (max (score) for course in ('+ @ SQL +') B ')
Go
-- SQL2000
Select name as name, class,
Max (Case course when 'China' then score else 0 end) language,
Max (Case course when 'mate' then score else 0 end) math,
Max (Case course when 'physical 'Then score else 0 end) Physical
From TB
Group by name, class