T-SQL Syntax: Row-to-column (pivot) and column-changing (UNPIVOT)

Before we know the use of the pivot relational operator, we cooperate with the case by aggregating the function ... When the wording to achieve the corresponding function, Microsoft in SQL Server 2005 and later version of the can pivot relational operator, Povit provides syntax than a series of select ... The syntax specified in the case statement is simpler and more readable.

Full syntax for Povit:

Table_source PIVOT (Aggregation function (<value_column>) for pivot_column in (<pivot_column_list>)) as <pivot_table_ Name>

Next we pass Select ... Case and pivot two syntax to complete the function of row to column.

Example:

1. Create a table

if object_id('TB') is  not NULL Drop TableTBGoCreate TableTB (numberedintNamevarchar(Ten), Coursevarchar(Ten), scoreint)GoInsert  intoTbValues(1,'Zhang San','language', About)Insert  intoTbValues(2,'Zhang San','Mathematics', the)Insert  intoTbValues(3,'Zhang San','Physical', the)Insert  intoTbValues(4,'John Doe','language', About)Insert  intoTbValues(5,'John Doe','Mathematics', -)Insert  intoTbValues(6,'John Doe','Physical',94)GoSelect *  fromTb

Results:

2. SELECT ... Case notation

Selectname,Max( Case  whenCourse='language'  ThenScoresEnd) aslanguages,Max( Case  whenCourse='Mathematics'  ThenScoresEnd) asMathematics,Max( Case  whenCourse='Physical'  ThenScoresEnd) asPhysical fromTbGroup  byName

Results:

3. Pivot notation

Select *  from (Select from as-t pivot (max for in as p -- fractions (aggregated columns)--Courses (conversion columns)

Results:

Pivot has a depressed problem is that there is no way to specify the grouping column, tested to find that pivot is all columns except the aggregate column and transform column as a grouping column, through the example to demonstrate our guess

Select  from TB pivot (max for in as P

Results:

Unpivot is used to convert columns to rows, and it's a lot easier to make a row, usually by unoin all

Unpivot Syntax: Table_source PIVOT (<value_column> for Pivot_column in (<pivot_column_list>)) as <pivot_table _name>

Example:

1. Create a table

if object_id('TB') is  not NULL Drop TableTBGoCreate TableTB (namevarchar(Ten), languageintMathematicalintPhysicalint)Insert  intoTbValues('Zhang San', About, the, the)Insert  intoTbValues('John Doe', About, -,94)GoSelect *  fromTb

Results:

2. UNION all notation

Select *  from(    SelectName'language'  asCourses, Languages asScores fromTBUnion  All    SelectName'Mathematics'  asCourses, Mathematics asScores fromTBUnion  All    SelectName'Physical'  asCourses, physics asScores fromTB) asTOrder  byName

Results:

3, the Unpivot wording

Select *  from  for inch  as T

Results:

Summary: Previously also used in pivot, the results in the recent writing code when not remember how to write, today will pivot and Unpivot detailed usage record down, hope to be able to remember, good memory than bad pen

T-SQL Syntax: Row-to-column (pivot) and column-changing (UNPIVOT)