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

Source: Internet
Author: User
Tags case statement

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)

Contact Us

The content source of this page is from Internet, which doesn't represent Alibaba Cloud's opinion; products and services mentioned on that page don't have any relationship with Alibaba Cloud. If the content of the page makes you feel confusing, please write us an email, we will handle the problem within 5 days after receiving your email.

If you find any instances of plagiarism from the community, please send an email to: info-contact@alibabacloud.com and provide relevant evidence. A staff member will contact you within 5 working days.

A Free Trial That Lets You Build Big!

Start building with 50+ products and up to 12 months usage for Elastic Compute Service

  • Sales Support

    1 on 1 presale consultation

  • After-Sales Support

    24/7 Technical Support 6 Free Tickets per Quarter Faster Response

  • Alibaba Cloud offers highly flexible support services tailored to meet your exact needs.