SQL Row to Column

Let's say a student score table (TB) is as follows:

Name Course Score
Zhang San language 74
Zhang San Mathematics 83
Zhang San Physics 93
John Doe Language 74
John Doe Mathematics 84
John Doe Physics 94
Want to become (get the following result):
Name Chinese mathematics Physics
---- ---- ---- ----
Lee 474 84 94
Sheet 374 83 93
-------------------

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)
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 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)

--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 ')

---------------------------------

--sql SERVER 2000 Static SQL.
Select name and 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) Physics,
Cast (avg (fractional *1.0) as decimal (18,2)) average,
SUM (Score) Total
From TB
Group BY name

--sql SERVER 2000 Dynamic SQL.
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 + ', CAST (avg (fractional *1.0) as decimal (18,2)) average, sum (score) total from TB group by name '
EXEC (@sql)

--sql SERVER 2005 Static SQL.
Select M.*, N. average, N. Total score from
(SELECT * FROM (SELECT * from TB) a pivot (max (score) for course in (language, mathematics, physics)) b) m,
(select Name, CAST (avg (fractional *1.0) as decimal (18,2)) average, sum (score) total from TB Group by name) n
where M. Name = N. Name

--sql SERVER 2005 Dynamic SQL.
DECLARE @sql varchar (8000)
Select @sql = isnull (@sql + ', ', ') + courses from the TB group by course
EXEC (' select m.*, N. average, N. Total score from
(SELECT * FROM (SELECT * from TB) a pivot (max (score) for course in (' + @sql + ')) b) m,
(select Name, CAST (avg (fractional *1.0) as decimal (18,2)) average, sum (score) total from TB Group by name) n
where M. Name = N. Name ')

DROP table TB

------------------
------------------

CREATE table TB (name varchar (10), language int, math int, physical int)
INSERT into TB values (' Zhang San ', 74,83,93)
INSERT into TB values (' John Doe ', 74,84,94)
Go

--sql SERVER 2000 Static SQL.
SELECT * FROM
(
Select name, course = ' language ', score = language from TB
UNION ALL
Select name, course = ' math ', score = math from TB
UNION ALL
Select name, course = ' physical ', score = physical from TB
) T
Order BY name, case course when ' language ' then 1 when ' math ' then 2 when ' physical ' then 3 end

--sql SERVER 2000 Dynamic SQL.
--Call system table dynamic Ecology.
DECLARE @sql varchar (8000)
Select @sql = isnull (@sql + ' union All ', ') + ' select name, [course] = ' + QuoteName (name, ' "') + ', [score] = ' + Quoten Ame (Name) + ' from TB '
From syscolumns
where name! = N ' name ' and ID = object_id (' TB ')--table name TB, not containing other columns named name
ORDER BY colid ASC
EXEC (@sql + ' ORDER by name ')

--sql SERVER 2005 Dynamic SQL.
Select Name, course, score from TB Unpivot (score for course in ([Language], [math], [physical])) T

--sql Server 2005 Dynamic SQL, with SQL Server 2000 dynamic SQL.

--------------------

SELECT * FROM
(
Select name as name, course = ' language ', score = language from TB
UNION ALL
Select name as name, course = ' math ', score = math from TB
UNION ALL
Select name as name, course = ' physical ', score = physical from TB
UNION ALL
Select name as name, course = ' average ', fraction = cast ((language + math + physics) *1.0/3 as Decimal (18,2)) from TB
UNION ALL
Select name as name, course = ' total scores ', score = language + math + physics from TB
) T
Order BY name, case course when ' language ' then 1 when ' math ' then 2 when ' physical ' then 3 when ' average ' then 4 when ' total ' then 5 end

SQL Row to Column