The ranks of each other

Definition:

Pivot English Meaning: Rotary Motion

PIVOT used to rotate column values to column names (row to column),

UN PIVOT used to convert column names to column values (that is, column change).

can also in SQL Server - you can use aggregate functions to mate Case Statement Implementation .

Syntax:

PIVOT and the UN PIVOT The syntax difference is whether there is a use of aggregate functions

PIVOT:

Table_sourcepivot (Aggregation function (value_column) for Pivot_columnin (<column_list>))

UNPIVOT:

Table_sourceunpivot (value_columnfor pivot_columnin (<column_list>))

Note:

PIVOT, Unpivot is the syntax for SQL Server 2005 .

Example:first, row to column

1. Create a form

If object_id (' test ') is not null drop table testgocreate table test (name varchar (10), course varchar (10), fractional int) insert INTO Test V Alues (' Zhang San ', ' Chinese ', ') insert into test values (' Zhang San ', ' math ', ') insert into test values (' Zhang San ', ' English ', ') ' INSERT into test values (' John Doe ', ' language ', ' + ' insert into test values (' John Doe ', ' math ', ') insert into test values (' John Doe ', ' English ', ' goselect ') * from test

Name Course Score

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

Zhang San language 65

Zhang San Mathematics 85

Zhang San English 70

John Doe Language 80

John Doe Mathematics 71

John Doe English 83

( 1 ), through Aggregation function Mates Case Statement Implementation :

Select name, max (case course when ' language ' then score else 0 end) language, Max (case course when ' math ' then score else 0 end) Math, Max (case course when ' English ' Then fractions else 0 end) English from Testgroup by name

(2) , through pivot implementation:

SELECT * FROM Test pivot (MAX (score) for course in (Chinese, Math, English)) p

The results are consistent:

Name Chinese maths English

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

Lee 480 71 83

Sheet 365 85 70

second, the list of career change

1. Create a form

If object_id (' test ') is not null drop table testgocreate table test (name varchar (10), language int, math int, English int) insert INTO test VA Lues (' Zhang San ', 65,85,70) insert into test values (' John Doe ', 80,71,83) goselect * from test

Name Chinese maths English

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

Sheet 365 85 70

Lee 480 71 83

(1) , through Aggregation function Mates Case Statement Implementation :

SELECT * FROM (select name, course = ' language ', score = language from Test union ALL select Name, course = ' math ', score = Math from Test union ALL select Name, course = ' physical ', minutes Number = English from test) Porder by name, case course when ' language ' then 1 when ' math ' then 2 when ' English ' then 3 end

Name Course Score

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

John Doe Physics 83

John Doe Language 80

John Doe Mathematics 71

Zhang San Physics 70

Zhang San language 65

Zhang San Mathematics 85

(2) , through pivot implementation:

Select Name, course, score from Test Unpivot (score for course in ([Language],[Math],[English]) p

Name Course Score

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

John Doe Physics 83

John Doe Language 80

John Doe Mathematics 71

Zhang San Physics 70

Zhang San language 65

Zhang San Mathematics 85

This article is from the "Sukun" blog, make sure to keep this source http://sukunwu.blog.51cto.com/10453116/1677429

The ranks of each other