Using PIVOT and Unpivot to implement query results row to column

Grammar:

SELECT < non-pivot columns >,    < column names >,    < column names    >, ... < column name >, from    (< Generate data for SELECT query >)    < source query alias >PIVOT (    < Aggregate functions > (< Columns to aggregate >) for[< columns that contain values to be column headings >] in    ([First pivot column], [Second pivot column],    < Pivot table Aliases >< optional ORDER by clauses >;

Simulation data:

INSERT into week_income

SELECT‘Monday‘,1000UNIONAllSELECT‘Tuesday‘,2000UNIONAllSELECT‘Wednesday‘,3000UNIONAllSELECT‘Thursday‘,4000Union allSelect ' Friday ' ,theunion allSelect ' Saturday ', 6000UNION allSELECT ' Sunday ',7000       

The most common query we use most often is to query the income of each day or a few days of the week, for example, to query the full income from Monday to Sunday:

From Week_income

Get the following query result set:

WEEK INCOME
Monday on 1000
Tuesday on 2000
Wednesday on 3000
Thursday on 4000
Friday on 5000
Saturday on 6000
Sunday on 7000

But in some cases (often in some reports), we want to show revenue from Monday to Sunday on a single line, when the query result set should look like this:

Monday Tuesday Wednesday Thursday Friday Saturday Sunday
1000 2000 3000 4000 5000 6000 7000

In this case, SQL query statements can be written like this:

SELECT

SUM (Case WEEKWhen‘Monday‘Then INCOMEEND)As[Monday],SUM (Case WEEKWhen‘Tuesday‘Then INCOMEEND)As[Tuesday],SUM (Case WEEKWhen‘Wednesday‘Then INCOMEEND)As[Wednesday],SUM (Case WEEKWhen‘Thursday‘Then INCOMEEND)As[Thursday],SUM (Case WEEKWhen‘Friday‘Then INCOMEEND)As[Friday],SUM (Case WEEKwhen   Saturday  ' then INCOME end) Span style= "color: #0000ff;" >as [ Sunday ]from week_income              

However, a simpler approach is provided in SQL SERVER 2005, which is the PIVOT relational operator. (instead of "column change" is Unpivot), here is the SQL statement that uses pivot to implement "row-to-column"

SELECT[Monday],[Tuesday],[Wednesday],[Thursday],[Friday],[Saturday],[Sunday]From Week_incomePIVOT (SUM (INCOME)for [Week] in ([ Monday ],[ Tuesday ],[ Wednesday ],[ Thursday ],[ Friday ],[ Saturday ],[ Sunday ])) Tbl 
            

The query results under normal conditions are as follows:

Monday on 1000
Tuesday on 2000
Wednesday on 3000
Thursday on 4000
Friday on 5000
Saturday on 6000
Sunday on 7000

This is done after the row to column:

Monday Tuesday Wednesday Thursday Friday Saturday Sunday
1000 2000 3000 4000 5000 6000 7000

That is, after row to column, the value of the original column is changed to the column name, here is the value of the original week column "Monday", "Tuesday" ... "Sunday" side do the column name, and we need to do another job is to calculate the values of these columns ("Calculation" here is actually the aggregation function within the pivot (SUM,AVG, etc.))

SELECT[Monday],[Tuesday],[Wednesday],[Thursday],[Friday],[Saturday],[Sunday]--Here is the third step of pivot (the column of the result set after selecting row to column)You can use "*" to select all columns, or you can select only some columns (that is, some days)From Week_income--Here is the second step of 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 it is a subquery to specify the alias, otherwise syntax errorPIVOT (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 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      

Using PIVOT and Unpivot to implement query result row to column