How to convert rows into columns in SQL queries

The table has three columns: stunamem and subject score.
Table data includes:
Zhang San Language 0
Li Si Language 4
Wang 'er English 90
Zhang San, mathematics 333
The query results are as follows:
Chinese and English mathematics for students
Zhang San 0 0 333
Li Si 4 0 0
The default value of no score is 0.

Query SQL

Wait downstairs.

Use Case when

SQL code

/*

Title: normal row/column conversion (Version 2.0)

Author: AI xinjue Luo. Xin Hua)

Time: 2008-03-09

Location: Shenzhen, Guangdong Province

Note: normal row/column conversion (version 1.0) only provides static and dynamic writing for SQL Server 2000, and Version 2.0 adds SQL Server 2005 related writing.

Question: Suppose there is a student orders table (TB) as follows:

Name course score

Zhang San Language 74

James math 83

Zhang San physical 93

Li Si language 74

Li Si mathematics 84

Li Si physical 94

(The following result is displayed ):

Name, Chinese, Mathematics, Physics

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

Li Si 74 84 94

Zhang San 74 83 93

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

*/

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)

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

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

-- SQL Server 2005 static SQL.

Select * from (select * from TB) A between (max (score) for course in (Chinese, Mathematics, 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 (select * from TB) a round (max (score) for course in ('+ @ SQL +') B ')

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

/*

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

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

Drop table TB

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

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

/*

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 Tb (name varchar (10), Chinese int, mathematical int, physical INT)

Insert into TB values ('zhang san', 93)

Insert into TB values ('Lee 4', 94)

Go

-- SQL Server 2000 static SQL.

Select * from

(

Select name, course = 'China', score = Chinese from TB

Union all

Select name, course = 'mat', score = mathematics from TB

Union all

Select name, course = 'physical ', score = physical from TB

) T

Order by name, Case course when 'China' then 1 when' math 'then 2 when' then 3 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 tb'

From syscolumns

Where name! = N'name' and ID = object_id ('tb') -- 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 TB unaligned (score for course in ([language], [mathematics], [physics]) T

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

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

/*

Problem: add an average score and the total score to the above result. The following result is obtained:

Name course score

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

Li Si language 74.00

Li Si, mathematics 84.00

Li Si physical 94.00

Li Si average score 84.00

Li Si's total score is 252.00

Zhang San Chinese 74.00

Zhang San, mathematics 83.00

Zhang San physical 93.00

Michael Jacob has an average score of 83.33.

Zhang San's total score is 250.00

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

*/

Select * from

(

Select name as name, course = 'China', score = Chinese from TB

Union all

Select name as name, course = 'mat', score = mathematics from TB

Union all

Select name as name, course = 'physical ', score = physical from TB

Union all

Select name as name, course = 'average', score = cast (Chinese + mathematics + physics) * 1.0/3 as decimal () from TB

Union all

Select name as name, course = 'Total', score = Chinese + mathematics + physics from TB

) T

Order by name, Case course when 'China' then 1 when' math 'then 2 when' 'Then 3 when' average score 'then 4 when' total score 'then 5 end

Drop table TB

SQL code

Create Table # Test (stunamem varchar (10), subject varchar (10), score INT)

Insert # test select 'zhang san', 'China', 0

Insert # test select 'Li si', 'China', 4

Insert # test select 'wang 2', 'English ', 90

Insert # test select 'zhang san', 'mat', 333

Select stunamem, max (case when subject = 'China' then score else 0 end)

, Max (case when subject = 'English 'Then score else 0 end)

, Max (case when subject = 'mate' then score else 0 end)

From # Test

Group by stunamem

Stunamem

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

Li Si 4 0 0

Wang er0 90 0

Zhang San 0 0 333

(3 rows affected)

If it is 2005, you can use the functions.
SQL code

, [1996], [1997], [1998]

From DBO. Orders

Partition (

Sum (Quantity)

For [year] in ([1996], [1997], [1998])

) X

-- Result:

Custom ID 1996 1997 1998

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

Anton 24 295 40

Bonap 181 486 313

Bottm 81, 454, 421

For more information, see use of the SQL Server 2005 limit operator.

[Code = SQL]
Use tempdb
Go
Select Year (orderdate) as [year]
, Customerid
, OD. Quantity
Into DBO. Orders
From northwind... orders as O
Join northwind... [Order Details] As OD
On O. orderid = OD. orderid
Where o. customerid in ('bonap ', 'bottm', 'anton ')

Select customerid
, [1996], [1997], [1998]
From DBO. Orders
Partition (
Sum (Quantity)
For [year] in ([1996], [1997], [1998])
) X

-- Result:
Custom ID 1996 1997 1998
-------------------------------------------
Anton 24 295 40
Bonap 181 486 313
Bottm 81, 454, 421

[/Code]

Blog: http://www.cnblogs.com/www56/archive/2010/11/06/1870576.html