Row to column: SQL SERVER pivot and usage explanation

Original: Row to column: SQL SERVER pivot and usage explanation

In database operations, there are times when we encounter the need to implement "row-to-column", for example, a table for a weekly income statement for a store:

VARCHAR (Ten) DECIMAL)

Let's insert some simulation data first:

INSERT  intoWeek_incomeSELECT 'Monday', +UNION  AllSELECT 'Tuesday', -UNION  AllSELECT 'Wednesday', theUNION  AllSELECT '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:

SELECT  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( CaseWEEK when 'Monday'  ThenINCOMEEND) as [Monday],SUM( CaseWEEK when 'Tuesday'  ThenINCOMEEND) as [Tuesday],SUM( CaseWEEK when 'Wednesday'  ThenINCOMEEND) as [Wednesday],SUM( CaseWEEK when 'Thursday'  ThenINCOMEEND) as [Thursday],SUM( CaseWEEK when 'Friday'  ThenINCOMEEND) as [Friday],SUM( CaseWEEK when 'Saturday'  ThenINCOMEEND) as [Saturday],SUM( CaseWEEK when 'Sunday'  ThenINCOMEEND) as [Sunday] fromWeek_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] fromWeek_incomePIVOT (SUM(INCOME) for[Week]inch([Monday],[Tuesday],[Wednesday],[Thursday],[Friday],[Saturday],[Sunday])) TBL

Please refer to the usage of pivot in MSDN:

http://technet.microsoft.com/zh-cn/library/ms177410 (v=sql.105). aspx

But the description on MSDN is too prescriptive, and I haven't figured out how to use pivot for a while, and I can't figure out what the syntax means in pivot. So Google has a lot of information, as well as through the above-mentioned Week_income table examples were tested, and finally made clear of its usage. There's a blog post on the Internet that explains it well: T-SQL pivot parsing and combat, basically what I'm going to write is to refer to the blog post, plus a little personal understanding.

To understand the pivot syntax is to be clear about why Microsoft is designing pivot, but I believe that the reality of the demand for design ideas, so in the end we still have to figure out what is "row to column":

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.))

Now let's analyze the pivot syntax in conjunction with annotations (it's a good idea to look at what I mentioned above: T-SQL pivot Parsing and combat, the three steps of the pivot syntax are very important):

SELECT [Monday],[Tuesday],[Wednesday],[Thursday],[Friday],[Saturday],[Sunday]--Here is the third step of the 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) fromWeek_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]inch([Monday],[Tuesday],[Wednesday],[Thursday],[Friday],[Saturday],[Sunday])--Here is the first step of the pivot, also the core of the 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--alias must be written

The above is my understanding of pivot, and I do my best to express it. But then again, the personal understanding of the way is different, as I began to read a lot of blog posts, did not understand the pivot usage. The result is still hard through the example and other people's blog add thinking to understand, so if you read this post still do not understand, it is normal, with examples, coupled with their own thinking, slowly will be able to understand.

Row to column: SQL SERVER pivot and usage explanation