Pivot and Unpivot in T-SQL

Write in front

Today rest, take time to understand the next pivot and Unpivot, remember the teacher when the row to the column, it seems mentioned, but he said the most is "this you can learn privately, very simple ...", simple you do not say, do not spit groove him, or well tidy up the content of the morning self-study.

Pivot operator for pivot conversion

Perspective transformation refers to the processing of a state in which data is converted from rows to columns. The main experience is three logical processing stages (grouping, scaling, aggregation).

SQL2005 introduces the table's operator-pivot, similar to other table operators (such as join), which also performs operations in the context of the FROM clause of a query, primarily manipulating a table expression on a table, and returning a result table.

The pivot operator has important places to note: You do not have to display the specified grouping element for him, or the goup by clause, because it implicitly assigns those elements that are not specified as extension elements or as aggregation elements as grouping elements.

Create the student table as follows:

Use the pivot row to go to the column statement as follows:

Select Stuname,isnull (Chinese,0 as language, isnull (mathematics,0) as mathematics, IsNull (English,0 as  from Student    for inch (Chinese, Maths, English)) P

Here Stugrade as the aggregation element, stusubject as an extension element, so the remaining stuname are automatically grouped elements

The results are as follows:

Unpivot operator for Inverse perspective transformation

Inverse perspective is a means of converting data from columns to rows. Mainly through three logical processing stages (generating replicas, extracting elements, deleting unrelated intersections)

Note that the pivot conversion of the table and then the inverse perspective conversion, and will not get the original table, because the inverse perspective is only the perspective transformation of the table to convert a format, but after the perspective transformation and then through the inverse perspective can restore the table information, that is, the aggregation operation in the perspective transformation will lose the details of the original table, The inverse perspective does not lose any information. (because there is no aggregate operation)

You need to borrow the result set from the perspective transformation. The code is as follows:

With CC as(SelectStuname,isnull (Mathematics,0) asMathematics, IsNull (Chinese,0) asChinese, isnull (English,0) asEnglish from(SelectStuname,isnull (Chinese,0) asChinese, isnull (mathematics,0) asMathematics, IsNull (English,0) asEnglish fromStudent Pivot (min (stugrade) forStusubjectinch(Chinese, maths, English ))SelectStuname,stusubject,stugrade fromcc Unpivot (Stugrade forStusubjectinch(Chinese, Maths, English)) asS

This creates a temporary result set with a CTE, noting that the difference between Upivot and pivot is that it is not necessary to be aggregated, so it is well remembered.

Summarize

The teacher said that the database is very important technology, the work can not be separated from it, so I want to study hard, later encountered do not understand must ask, speak out loudly. There is usually do homework time, do not do, do not rush to find the answer, so although made to, oneself also forget quickly ah, many think, more practice, say more (this see occasion, should say must suppress live), refueling!

Pivot and Unpivot in T-SQL