Pivot row and column conversions in SQL

Pivot row and column conversions in SQL This article is guided by: in T-SQL statements, the pivot operator is used to rotate or pivot data between columns and rows, and the pivot command can implement a column change of a data table while performing an aggregation operation, and Unpivot, in contrast, implements row-to-column data.

Pivot rotates table-valued expressions by converting unique values in one column of an expression to multiple columns in the output, and performs aggregations if necessary for any remaining column values that are required in the final output. Unpivot performs the reverse operation with pivot, converting columns of table-valued expressions to column values.

Easy to say: Pivot is the row to the column, Unpivot is biographies line

First, pivot instance

1. Build a table

Create a sales Situation table, where the Year field represents the years, the quarter field represents the quarter, and the Amount field represents the sales. The quarter fields are used Q1, Q2, Q3, Q4 for one or two and three or four quarters respectively.

SQL CodeCopy

CREATE TABLE Salesbyquarter

    (    year INT,    --years

        Quarter CHAR (2),  --Quarterly

        Amount Money  --Total

    )

2. Fill in the table data

Fill in the table data using the following procedure.

SQL CodeCopy

SET NOCOUNT on

    DECLARE @index INT

    DECLARE @q INT

    SET @index = 0

    DECLARE @year INT

    while (@index < 30)

    BEGIN

        SET @year = 2005 + (@index% 4)

        SET @q = (CAST ((RAND () *) as INT)% 4) + 1

        INSERT into Salesbyquarter VALUES (@year, ' Q ' + CAST (@q as CHAR (1)), RAND () * 10000.00)

        SET @index = @index + 1

    END

3, if we want to compare the annual sales status of each quarter, how to do? There are two ways to do this:

(1), Case statement query using the traditional select

In previous versions of SQL Server, converting row-level data to column-level data would need to use a series of case statements and aggregate queries. While this approach gives developers the ability to take a high level of control over the returned data, writing these queries can be a cumbersome task.

SQL CodeCopy

    SELECT Year as years

        , sum (case if quarter = ' Q1 ' then amount else 0 end) Quarter

        , sum (case if quarter = ' Q2 ' then amount else 0 end) Two quarter

        , sum (case if quarter = ' Q3 ' then amount else 0 end) Three quarter

        , sum (case if quarter = ' Q4 ' then amount else 0 end) Four quarter

    From Salesbyquarter GROUP by year ORDER by year DESC

The results are as follows:

(2), using pivot

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

SQL CodeCopy

SELECT year as years, Q1 as first quarter, Q2 as two quarter, Q3 as three quarter, Q4 as four quarter from Salesbyquarter PIVOT (SUM (amount) for quarter in (Q1, Q2, Q3, Q4)) as P ORDER by year DESC

The results are as follows:

Second, the process of pivot is described in detail by one of the following examples

SQL CodeCopy

Select [Monday],[Tuesday],[Wednesday],[Thursday],[Friday],[Saturday],[Sunday]--Here is the third step of pivot (the column of the result set after row-to-column selection) You can use "*" to select all columns, or select only some columns (that is, some days)

From Week_income-This is the second step of the pivot (prepare the original query result, because pivot is a conversion operation on an original query result set, so query a result set first) here can be a select subquery, but to specify an alias for a subquery, Otherwise syntax error

PIVOT

(

    SUM (INCOME) for [week] in ([Monday],[Tuesday],[Wednesday],[Thursday],[Friday],[Saturday],[Sunday])--here is the pivot first step, also the core place, for row to column operation. The aggregate function sum indicates how you want to handle the value of the converted column, whether it is the sum (sum), average (avg), Min,max, and so on. For example, if the Week_income table has two data 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 row to column is of course 1500. After for [week] in ([Monday],[Tuesday] ...) In for [week] means that the values of the week column are converted to columns, which is "column by value." But the values that need to be converted into columns can be many, and we just want to take a few of them into columns, so how do we take them? is in inside, for example, I just want to see the income of the working day, in inside only write "Monday" to "Friday" (note, in Inside is the original week column value, "column value"). In general, SUM (INCOME) for [week] in ([Monday],[Tuesday],[Wednesday],[Thursday],[Friday],[Saturday],[Sunday]) The meaning of this sentence is to say: the column [Week] value is "Monday", "Tuesday", " Wednesday "," Thursday "," Friday "," Saturday "," Sunday "are converted into columns respectively, and the values of these columns are taken as the sum of income.

) tbl--aliases must be written

three. UNPIVOTClearly, the UN prefix indicates that it does the opposite of pivot, that is, a career change. The Unpivot operation involves the following three logical processing stages. 1, generating replicas2, extracting elements3, delete the row with null UNPIVOT instances SQL Code replication

CREATE TABLE PVT (vendorid int, Emp1 int, Emp2 int,    Emp3 int, Emp4 int, Emp5 int); Goinsert 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 in To 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, Ordersfrom    (select VendorID, EMP1, EMP2, Emp3, Emp4, EMP5 from   Pvt) Punpivot   (Orde RS for Employee in       (EMP1, EMP2, Emp3, Emp4, EMP5)) as UNPVT; GO

Analysis of the above UNPIVOT instances

The input of 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 the Unpivot. Because the IN clause here has 5 column names, 5 copies are generated for each source row. The result is a new column in the virtual table that will be used to save the name of the source column in string format (for and in, the above example is Employee). In the second step, the row corresponding to the column name is extracted from the source column based on the value in the new column. The third step, delete the result column value is null row, complete this query.

Pivot row and column conversions in SQL