Another method for SQL row and column conversions

CREATE table TB (name varchar (10), course varchar (10), fractional int)
INSERT into TB values (' Zhang San ', ' language ', 74)
INSERT into TB values (' Zhang San ', ' math ', 83)
INSERT into TB values (' Zhang San ', ' physical ', 93)
INSERT into TB values (' John Doe ', ' language ', 74)
INSERT into TB values (' John Doe ', ' math ', 84)
INSERT into TB values (' John Doe ', ' physical ', 94)
INSERT into TB values (' Harry ', ' physical ', 90)
INSERT into TB values (' six ', ' physical ', 100)
Go
--sql SERVER 2000 Static SQL, refers to the course only language, mathematics, physics, this course. (hereinafter)
Select name as name,
Max (case course when ' language ' then score else 0 end) language,
Max (case course when ' math ' then fraction else 0 end) Math,
Max (case course when ' physical ' then fraction else 0 end) physical
From TB
Group BY name
--sql SERVER 2000 Dynamic SQL, refers to the course of more than language, mathematics, physics, this course. (hereinafter)
DECLARE @sql varchar (8000)
Set @sql = ' Select Course '
Select @sql = @sql + ', max (case name when "+ name +" then score else 0 end) [' + name + '] '
From (select distinct name from TB) as a
Print @sql
Set @sql = @sql + ' from TB GROUP by course '
EXEC (@sql)
Go
DECLARE @sql varchar (8000)
Set @sql = ' Select Name '
Select @sql = @sql + ', max (case course when ' + course + ' then score else 0 end) [' + Course + '] '
From (select DISTINCT course from TB) as a
Set @sql = @sql + ' from TB GROUP by name '
EXEC (@sql)
Go
SELECT * FROM TB
Go

--because there is no environment for SQL 2005, the following untested

--sql SERVER 2005 Static SQL.

SELECT * FROM (SELECT * from TB) a pivot (max (score) for course in (language, mathematics, physics)) b

--sql SERVER 2005 Dynamic SQL.

DECLARE @sql varchar (8000)

Select @sql = isnull (@sql + '],[', ') + courses from the TB group by course

Set @sql = ' [' + @sql + '] '

EXEC (' SELECT * from "(SELECT * from TB) a pivot (max (score) for course in (' + @sql + ')) B ')

Another method for SQL row and column conversions