SQL normal row and column conversion _mssql

Question: Suppose there is a student score table (TB) as follows:
Name Course Score
John Language 74
John Math 83
John Physics 93
Dick Language 74
Dick Math 84
Dick Physics 94
Want to become (get the following results):
Name Chinese mathematics Physics
---- ---- ---- ----
Lee 474 84 94
Zhang 374 83 93
-------------------
*/
CREATE table TB (name varchar (10), course varchar (10), score int)
INSERT into TB values (' John ', ' language ', 74)
INSERT into TB values (' John ', ' math ', 83)
INSERT into TB values (' John ', ' Physics ', 93)
INSERT into TB values (' Dick ', ' language ', 74)
INSERT into TB values (' Dick ', ' math ', 84)
INSERT into TB values (' Dick ', ' physics ', 94)
Go
--sql SERVER 2000 Static SQL, refers to the course only the three courses of language, mathematics, physics. (in the same below)
Select name as name,
Max (case course when ' language ' then score else 0 end) language,
Max (case course when ' math ' then score else 0 end) Mathematics,
Max (case course when ' physical ' then score else 0 end) Physics
From TB
Group BY name
--sql SERVER 2000 Dynamic SQL, which refers to courses with more than the three courses of language, mathematics and physics. (in the same below)
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 (fractions) for course in (Chinese, maths, physics)) b
--sql SERVER 2005 Dynamic SQL.
DECLARE @sql varchar (8000)
Select @sql = isnull (@sql + '],[', ') + course from TB GROUP by course
Set @sql = ' [' + @sql + '] '
EXEC (' SELECT * from TB ') a pivot (max (score) for course in (' + @sql + '))
---------------------------------
/*
Problem: On the basis of the above results, add the average score, and get the following results:
The average score of mathematical physics in the name of Chinese
---- ---- ---- ---- ------ ----
Lee 474 84 94 84.00 252
Zhang 374 83 93 83.33 250
*/
--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 score else 0 end) Mathematics,
Max (case course when ' physical ' then score else 0 end) Physics,
Cast (avg (fractional *1.0) as decimal (18,2)) is divided evenly,
SUM (score) Total score
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 points, N. Total score from
(SELECT * from TB) a pivot (max (score) for course in (Chinese, maths, physics)) m,
(select Name, CAST (avg (fractional *1.0) as decimal (18,2)) Average score, 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 + ', ', ') + course from TB GROUP by course
EXEC (' select m.*, N. Average score, N. Total score from
(SELECT * from TB) a pivot (max (fractions) for course in (' + @sql + ')) m,
(select Name, CAST (avg (fractional *1.0) as decimal (18,2)) Average score, sum (score) total from TB Group by name) n
where m. Name = N. Name ')
DROP table TB
------------------
------------------
/*
Question: If the above two tables change each other: the table structure and data are:
Name Chinese mathematics Physics
Zhang 374 83 93
Lee 474 84 94
Want to become (get the following results):
Name Course Score
---- ---- ----
Dick Language 74
Dick Math 84
Dick Physics 94
John Language 74
John Math 83
John Physics 93
--------------
*/
CREATE table TB (name varchar (10), language int, math int, physical int)
INSERT into TB values (' John ', 74,83,93)
INSERT into TB values (' Dick ', 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 = ' physics ', 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 the 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 with no 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], [physics]) t
--sql Server 2005 Dynamic SQL, with SQL Server 2000 dynamic SQL.
--------------------
/*
Question: Add an average score to the above results, and get the following results:
Name Course Score
---- ------ ------
Dick Language 74.00
Dick Math 84.00
Dick Physics 94.00
Li Shiping 84.00
Dick Total Score 252.00
John Language 74.00
John Math 83.00
John Physics 93.00
John Average score 83.33
John Total Score 250.00
------------------
*/
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 = ' physics ', score = physical from TB
UNION ALL
Select name as name, course = ' average score ', score = cast ((language + math + physics) *1.0/3 as Decimal (18,2)) from TB
UNION ALL
Select name as name, course = ' total score ', 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 points ' then 4 when ' total score ' then 5 end
DROP table TB