SQL Server row and column Conversion

Go
Create Table Tb (name varchar (10), course varchar (10), score INT)
Insert into TB values ('zhang san', 'China', 74)
Insert into TB values ('zhang san', 'mat', 83)
Insert into TB values ('zhang san', 'Physical ', 93)
Insert into TB values ('Li si', 'China', 74)
Insert into TB values ('Li si', 'mat', 84)
Insert into TB values ('lily', 'Physical ', 94)
Insert into TB values ('lily', 'inc', 100)
Go
Select
*
From
TB
Go
-- SQL Server 2000 static SQL indicates that the course only includes three courses: Chinese, mathematics, and physics. (Same as below)
Select name as name,
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
Go
-- SQL Server 2000 dynamic SQL refers to three courses, including Chinese, mathematics, and physics. (Same as 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
Set @ SQL = @ SQL + 'from TB group by name'

Exec (@ SQL)
Go
-- SQL Server 2005 static SQL.
Select * from (select * from TB) A plus (max (score) for course in (Chinese, mathematics, physics, English) B

-- SQL Server 2005 dynamic SQL.
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

/*
Problem: Based on the above results, the average score and total score are added. The following result is obtained:
Name, Chinese, mathematics, and physics average score
--------------------------
Li Si 74 84 94 84.00 252
Zhang San 74 83 93 83.33 250
*/

-- SQL Server 2000 static SQL.
Select name,
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) physics,
Cast (AVG (score * 1.0) as decimal () average score,
Sum (score) total score
From TB
Group by name
Go

-- 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
Set @ SQL = @ SQL + ', average score of cast (AVG (score * 1.0) as decimal (), total sum (score) from TB group by name'
Exec (@ SQL)

-- SQL Server 2005 static SQL.
Select M. *, N. Average score, N. Total score from
(Select * from TB) A between (max (score) for course in (Chinese, Mathematics, Physics) B) m,
(Select name, cast (AVG (score * 1.0) as decimal () average score, sum (score) total score 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 round (max (score) for course in ('+ @ SQL +') B) m,
(Select name, cast (AVG (score * 1.0) as decimal () average score, sum (score) total score from TB group by name) N
Where M. Name = n. name ')
Go

/*
Question: If the two tables change each other: the table structure and data are:
Name, Chinese, Mathematics, Physics
Zhang San 74 83 93
Li Si 74 84 94
(The following result is displayed ):
Name course score
------------
Li Si language 74
Li Si mathematics 84
Li Si physical 94
Zhang San Language 74
James math 83
Zhang San physical 93
--------------
*/

Create Table tb1 (Name: varchar (10), Chinese int, mathematical int, physical INT)
insert into tb1 values ('zhang san', 93)
insert into tb1 values ('Li si', 94)
go
select * From tb1
go
-- SQL Server 2000 static SQL.
select * from
(
select name as name, course = 'China ', score = Chinese from tb1
Union all
select name as name, course = 'mat ', score = mathematics from tb1
Union all
select name as name, course = 'physical ', score = physical from tb1
Union all
select name as name, course = 'average', score = cast (Language + mathematics + physics) * 1.0/3 as decimal () from tb1
Union all
select name as name, course = 'Total ', score = Chinese + mathematics + Physical from tb1
) T
order by name, case course when 'chine' then 1 when' math 'then 2 when' then 3 when' average score 'then 4 when' total score 'then 5 end

-- SQL Server 2000 dynamic SQL.
-- Call the dynamic ecosystem of the system table.
Declare @ SQL varchar (8000)
Select @ SQL = isnull (@ SQL + 'Union all', '') + 'select name, [course] = '+ quotename (name, ''') + ', [score] = '+ quotename (name) +' from tb1'
From syscolumns
Where name! = N'name' and ID = object_id ('tb1 ') -- table name TB, excluding other columns whose names are names
Order by colid ASC
Exec (@ SQL + 'order by name ')

-- SQL Server 2005 dynamic SQL.
Select name, course, score from tb1 Untitled (score for course in ([language], [mathematics], [physics]) T

-- SQL Server 2005 dynamic SQL, same as SQL Server 2000 dynamic SQL.

Go

--> Test data: # T
If object_id ('tempdb. DBO. # t') is not null drop table # T
Create Table # T (Project nvarchar (5), value int, position INT)
Insert into # T
Select 'Project 1', 15, 1 Union all
Select 'Project 1', 34,2 Union all
Select 'Project 1', 56,3 Union all
Select 'Project 1', 44-union all
Select 'Project 2', 56,1 Union all
Select 'Project 2', 67,2 Union all
Select 'Project 2', 31,3 Union all
Select 'Project 2', 89,4 Union all
Select 'Project 3', 45, 1 Union all
Select 'Project 3', Union all
Select 'Project 3', 8, 3 Union all
Select 'Project 3', 23,4
Go
Select * from # T
Go
Declare @ SQL varchar (8000)
Set @ SQL =''
Select @ SQL = @ SQL + ', max (case when project = ''' + project + ''' then value end) [' + project + ']'
From # T group by project

Set @ SQL = stuff (@ SQL, 1, 1 ,'')

Exec ('select' + @ SQL + ', location from # T group by location ')

Go