SQL Server row and column conversion Pivot UnPivot

Pivot is used to rotate column values to column names (row to column), and SQL Server 2000 can be implemented with an aggregate function with a case statement

The general syntax for pivot is: Pivot (aggregate function (column) for column in (...)) As P

Full syntax:

Table_source

PIVOT (

Aggregation function (value_column)

For Pivot_column

In (<column_list>)

)

Unpivot is used to convert columns to column values (that is, column change), which can be implemented with union in SQL Server 2000

Full syntax:

Table_source

UNPIVOT (

Value_column

For Pivot_column

In (<column_list>)

)

Note: PIVOT, Unpivot is SQL Server 2005 syntax to use to modify the database compatibility level
In the database properties, options, compatibility level, change to 90

Typical examples

First, row to column

1. Create a form

ifobject_id (' TB ') ISNOTNULLDROPTABLETB

Go

CREATETABLETB (name varchar (10), course varchar (10), fractional int)

Insertintotbvalues (' Zhang San ', ' language ', 74)

Insertintotbvalues (' Zhang San ', ' mathematics ', 83)

Insertintotbvalues (' Zhang San ', ' physics ', 93)

Insertintotbvalues (' John Doe ', ' language ', 74)

Insertintotbvalues (' John Doe ', ' mathematics ', 84)

Insertintotbvalues (' John Doe ', ' physics ', 94)

Go

Select*fromtb

Go

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

2. Using SQL Server 2000 static SQL

--c

Select Name,

Max (case course when ' language ' then score else0end) language,

Max (case course when ' math ' then fraction else0end) Math,

Max (case course when ' physical ' then fraction else0end) Physics

Fromtb

GroupBy Name

Name Chinese mathematics Physics

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

Lee 474 84 94

Sheet 374 83 93

3. Using SQL Server 2000 dynamic SQL

--sql SERVER 2000 Dynamic SQL, refers to the course of more than language, mathematics, physics, this course. (hereinafter)

--variables are assigned in SQL language order

[Email protected] (500)

[Email protected]= ' Select name '

[Email protected] [Email protected]+ ', max (case course when "+ Course +" then score else 0 end) [' + Course + '] '

From (selectdistinct course FROMTB) a--with from TB Group by course, sorted by course name by default

[Email protected] [Email protected]+ ' from TB Group by name '

EXEC (@sql)

--Using IsNull (), variables determine the dynamic part first

[Email protected] (8000)

[Email protected]=isnull (@sql + ', ', ') + ' max (case course when ' + course + ' then score else 0 end) [' + Course + '] '

From (selectdistinct course FROMTB) ASA

[Email protected]= ' Select name, ' [email protected]+ ' from TB Group by name '

EXEC (@sql)

Name Mathematical Physics language

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

Lee 484 94 74

Sheet 383 93 74

4. Using SQL Server 2005 static SQL

SELECT*FROMTB Pivot (max (score) for course in (language, mathematics, physics)) a

5. Using SQL Server 2005 Dynamic SQL

--Using stuff ()

[Email protected] (8000)

[Email protected]= '--Initialize variable @sql

[Email protected] [Email protected]+ ', ' + Course fromtbgroupby Course--Variable multi-value assignment

[Email Protected]=stuff (@sql, 1, 1, ')--Remove the first ', '

[Email protected]= ' SELECT * from TB pivot (MAX (score) for course in (' [email protected]+ ')) ' a '

EXEC (@sql)

--or use ISNULL ()

[Email protected] (8000)

–-get a collection of courses

[Email protected]=isnull (@sql + ', ', ') + Course Fromtbgroupby Course

[Email protected]= ' SELECT * from TB pivot (MAX (score) for course in (' [email protected]+ ')) ' a '

EXEC (@sql)

The results of the row and column are combined with the total score, the average

1. Using SQL Server 2000 static SQL

--sql SERVER 2000 Static SQL

Select Name,

Max (case course when ' language ' then score else0end) language,

Max (case course when ' math ' then fraction else0end) Math,

Max (case course when ' physical ' then fraction else0end) Physics,

SUM (Score) Total,

Average of CAST (avg (fractional *1.0) Asdecimal (18,2))

Fromtb

GroupBy Name

The average score of the mathematics physics of the name Chinese

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

Lee 474 84 94 252 84.00

Sheet 374 83 93 250 83.33

2. Using SQL Server 2000 dynamic SQL

--sql SERVER 2000 Dynamic SQL

[Email protected] (500)

[Email protected]= ' Select name '

[Email protected] [Email protected]+ ', max (case course when "+ Course +" then score else 0 end) [' + Course + '] '

From (selectdistinct course FROMTB) A

[Email protected] [Email protected]+ ', sum (score) Total, cast (avg (fractional *1.0) as decimal (18,2)) average from TB group by name '

EXEC (@sql)

3. Using SQL Server 2005 static SQL

Selectm.*,n. Total score, N. Average

From

(SELECT*FROMTB pivot (max (score) for course in (language, mathematics, physics)) m,

(select name, sum (score) Total, cast (AVG (score *1.0) Asdecimal (18,2)) Average score

Fromtb

GroupBy name) n

Wherem. Name =n. Name

4. Using SQL Server 2005 Dynamic SQL

--Using stuff ()

--

[Email protected] (8000)

[Email protected]= '--Initialize variable @sql

[Email protected] [Email protected]+ ', ' + Course fromtbgroupby Course--Variable multi-value assignment

--with Select @sql = @sql + ', ' + course from (SELECT DISTINCT course from TB) a

[Email Protected]=stuff (@sql, 1, 1, ')--Remove the first ', '

[Email protected]= ' Select m.*, N. Total score, N. Average from

(SELECT * FROM (SELECT * from TB) a pivot (max (score) for course in (' [email protected]+ ')) b) m,

(select name, sum (score) Total, cast (avg (fractional *1.0) as decimal (18,2)), average of the from TB Group by name) n

where M. Name = N. Name '

EXEC (@sql)

--or use ISNULL ()

[Email protected] (8000)

[Email protected]=isnull (@sql + ', ', ') + Course Fromtbgroupby Course

[Email protected]= ' Select m.*, N. Total score, N. Average from

(SELECT * FROM (SELECT * from TB) a pivot (max (score) for course in (' +

@sql + ')) b) m,

(select name, sum (score) Total, cast (avg (fractional *1.0) as decimal (18,2)), average of the from TB Group by name) n

where M. Name = N. Name '

EXEC (@sql)

Second, the list of career change

1. Create a form

ifobject_id (' TB ') ISNOTNULLDROPTABLETB

Go

CREATETABLETB (name varchar (10), language int, math int, physical int)

Insertintotbvalues (' Zhang San ', 74,83,93)

Insertintotbvalues (' John Doe ', 74,84,94)

Go

Select*fromtb

Go

Name Chinese mathematics Physics

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

Sheet 374 83 93

Lee 474 84 94

2. Using SQL Server 2000 static SQL

--sql SERVER 2000 Static SQL.

Select*from

(

Select name, course = ' language ', score = Chinese FROMTB

UnionAll

Select name, course = ' math ', score = Math FROMTB

UnionAll

Select name, course = ' physical ', score = physical FROMTB

) T

By name, case course when ' language ' then1when ' math ' then2when ' physical ' then3End

Name Course Score

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

John Doe Language 74

John Doe Mathematics 84

John Doe Physics 94

Zhang San language 74

Zhang San Mathematics 83

Zhang San Physics 93

2. Using SQL Server 2000 dynamic SQL

--sql SERVER 2000 Dynamic SQL.

--Call system table dynamic Ecology.

[Email protected] (8000)

[Email protected]=isnull (@sql + ' union All ', ') + ' select name, [Course]= '

+quotename (Name, "') + ', [score] = ' +quotename (name) + ' from TB '

Fromsyscolumns

wherename!= ' name ' andid=object_id (' TB ')--table name TB, not including other columns named name

Orderbycolid

EXEC (@sql + ' ORDER by name ')

Go

3. Using SQL Server 2005 static SQL

--sql SERVER 2005 Dynamic SQL

Select Name, course, score fromtb Unpivot (score for course in ([Language],[Math],[physics]) t

4. Using SQL Server 2005 Dynamic SQL

--sql SERVER 2005 Dynamic SQL

[Email protected] (4000)

[Email protected]=isnull (@sql + ', ', ') +quotename (Name)

Fromsyscolumns

whereid=object_id (' TB ') andnamenotin (' name ')

Orderbycolid

[Email protected]= ' Select name, [Course],[score] from TB Unpivot ([score] for [course] in (' [email protected]+ ')] B '

EXEC (@sql)

Reproduced above from http://www.cnblogs.com/zhangzt/archive/2010/07/29/1787825.html

SQL Server row and column conversion Pivot UnPivot