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)