In-depth SQL pivot row and column conversion detailed _ database other

Pivot rotates a table-valued expression by converting the unique value in a column of an expression to multiple columns in the output, and performs an aggregation if necessary on any remaining column values that are required in the final output. Unpivot performs the opposite operation with Pivot, converting the column of the table-valued expression to the column value.

Popular simple said: Pivot is row, Unpivot is the biography line

Examples of pivot

1. Table Building

Create a sales table where year fields represent years, quarter fields represent quarters, and Amount fields represent sales. Quarter fields are used Q1, Q2, Q3, and Q4 for one or two, three or four quarters respectively.

 CREATE TABLE Salesbyquarter
 (year INT,--years
  Quarter CHAR (2),-Quarterly amount Money-
  total
 )

2. Fill in the table data

Use the following procedure to fill in the table data.

Set NOCOUNT on
 DECLARE @index int
 DECLARE @q int
 SET @index = 0
 DECLARE @year int while
 (@index & Lt
 BEGIN
  Set @year = @q + (@index% 4)
  set = (CAST ((RAND () () as INT)% 4) + 1
  INSERT into S Alesbyquarter VALUES (@year, ' Q ' + CAST (@q as CHAR (1)), RAND () * 10000.00)
  SET @index = @index + 1

3, if we want to compare the annual quarterly sales situation, how to do? There are two ways to do this:

(1), using the traditional Select Case statement query

In previous versions of SQL Server, converting row-level data to column-level data used a series of case statements and aggregate queries. While this approach allows developers to have a high degree of control over the data returned, writing these queries is a hassle.

  SELECT year as years, sum (case when
  quarter = ' Q1 ' then amount else 0) quarter
  , sum (case when quarter = ' Q2 ' Then Amount else 0 end) Two quarter
  , sum (case when quarter = ' Q3 ' then amount else 0 end) Three quarter
  , sum (case when quarter = ' Q4 ' Then amount else 0 end ' Four quarter from salesbyquarter GROUP by year's order of
 DESC

The results obtained are as follows:

(2), using pivot

Because SQL Server 2005 has a new pivot operator, case statements and GROUP by statements are no longer needed. (each pivot query involves some kind of aggregation, so you can ignore the group by statement.) The pivot operator allows us to use case statement queries to achieve the same functionality, but you can do it with less code and look prettier.

SELECT year as, Q1 as quarter, Q2 as two quarter, Q3 as three quarter, Q4 as four quarter from Salesbyquarter PIVOT (SUM (amount) to quarter in (Q1, Q2, Q3, Q4)) as P order by year DESC

The results obtained are as follows:

Second, the process of pivot is introduced in detail through the following example

Select [Monday],[Tuesday],[Wednesday],[Thursday],[Friday],[Saturday],[Sunday]--This is pivot third step (a column that selects the result set after row-column) Here you can use "*" to indicate that you select all columns, or you can select only certain columns (that is, some days).
from Week_income--This is pivot the second step (prepare the original query result, because the pivot is a conversion operation on an original query result set, so query a result set out first) here can be a select subquery, However, when you specify an alias for a subquery, the syntax error
PIVOT
(
 SUM (income) for [Week] in ([Monday],[Tuesday],[Wednesday],[Thursday],[Friday],[Saturday],[Sunday])-- Here is the first step of pivot, and also the core place, for row-and-column operations. Aggregate function sum indicates how you want to handle the value of the converted column, sum (sum), average (avg), Min,max, and so on. For example, if there are two data in the Week_income table and its week is "Monday", one of the income is 1000, and the other income is 500, then the sum is used here, and the value of the column "Monday" after the row is set is 1500. Later for [week] in ([Monday],[Tuesday] ...) In for [week] means to convert the value of the week column to a column, which is "column by value." But there may be a lot of values that need to be converted into columns, and we just want to take a few of those values and convert them into columns, so how do we take them? is in inside, for example I just want to see the income of working day, in inside just write "Monday" to "Friday" (note, in inside is the value of the original week column, "In Value to column"). In general, SUM (income) for [Week] in ([Monday],[Tuesday],[Wednesday],[Thursday],[Friday],[Saturday],[Sunday]) The meaning of this sentence is "Monday", "Tuesday", "the column [Week] value", Wednesday ", Thursday", "Friday", "Saturday", "Sunday" are converted to columns, and the values of these columns take the sum of the income.
) tbl--alias must be written

Three. Unpivot

Obviously, the UN prefix indicates that it does the opposite of pivot, which is a column-changing career. The Unpivot operation involves the following three logical processing stages.

1, generating a copy
2, extract elements
3, delete rows with null

Unpivot Instance

CREATE TABLE PVT (vendorid int, Emp1 int, Emp2 int,
 Emp3 int, Emp4 int, Emp5 int);
Go
inserts into Pvt VALUES (1,4,3,5,4,4);
INSERT into Pvt VALUES (2,4,1,5,5,5);
INSERT into Pvt VALUES (3,4,3,5,4,4);
INSERT into Pvt VALUES (4,4,2,5,5,4);
INSERT into Pvt VALUES (5,5,1,5,5,5);
Go
--unpivot the table.
Select VendorID, Employee, Orders
from 
 (select VendorID, EMP1, EMP2, Emp3, Emp4, EMP5 from
 Pvt) p
UNP Ivot
 (Orders for Employee in 
  (EMP1, EMP2, Emp3, Emp4, EMP5)
) as UNPVT;
Go

Analysis of the above Unpivot example

The input of the

Unpivot is the left table expression p, the first step is to generate multiple copies of the rows in P, and a copy is generated for each column that appears in Unpivot. Because the IN clause here has 5 column names, you want to generate 5 copies for each source row. As a result, a new column will be added to the resulting virtual table to hold the name of the source column in string format (the example above is Employee). The second step is to extract the row corresponding to the column name from the source column according to the value in the new column. In the third step, delete the row with the result column value NULL to complete the query.