Shulou(Shulou.com)05/31 Report--

This article mainly introduces how to use the row-to-column SQL SERVER PIVOT. It is very detailed and has a certain reference value. Friends who are interested must finish it!

In database operations, we sometimes encounter the need to implement "row transfer". For example, the following table is the weekly income table of a store:

WEEK_INCOME (WEEK VARCHAR (10), INCOME DECIMAL)

Let's insert some simulation data first:

INSERT INTO WEEK_INCOME SELECT Monday, 1000UNION ALLSELECT Tuesday, 2000UNION ALLSELECT Wednesday, 3000UNION ALLSELECT Thursday, 4000UNION ALLSELECT Friday, 5000UNION ALLSELECT Saturday, 6000UNION ALLSELECT Sunday, 7000

Generally speaking, the most frequently used query is to query the income of each day or certain days of the week, such as the total income from Monday to Sunday:

SELECT WEEK,INCOME FROM WEEK_INCOME

The query result set is as follows:

WEEK INCOME

Monday 1000

Tuesday 2000

Wednesday 3000

Thursday 4000

Friday 5000

Saturday 6000

Sunday 7000

But in some cases (often in some reports), we want to show the revenue from Monday to Sunday in a row, 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, the SQL query statement can be written as follows:

SELECT SUM (CASE WEEK WHEN 'Monday' THEN INCOME END) AS [Monday], SUM (CASE WEEK WHEN 'Tuesday' THEN INCOME END) AS [Tuesday], SUM (CASE WEEK WHEN 'Wednesday' THEN INCOME END) AS [Wednesday], SUM (CASE WEEK WHEN 'Thursday' THEN INCOME END) AS [Thursday], SUM (CASE WEEK WHEN 'Friday' THEN INCOME END) AS [Friday], SUM (CASE WEEK WHEN 'Saturday' THEN INCOME END) AS [Saturday] SUM (CASE WEEK WHEN 'Sunday' THEN INCOME END) AS [Sunday] FROM WEEK_INCOME

However, an easier method is provided in SQL SERVER 2005, which is the "PIVOT" relational operator. (instead of "column wrapping" is UNPIVOT), here is the SQL statement that uses PIVOT to implement "row swapping".

SELECT [Monday], [Tuesday], [Wednesday], [Thursday], [Friday], [Saturday], [Sunday] FROM WEEK_INCOMEPIVOT (SUM (INCOME) for [week] in ([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 formal and serious. I haven't figured out how to use PIVOT for a long time. I can't figure out the meaning of the syntax in PIVOT. So google did a lot of data, and through the above-mentioned example of WEEK_INCOME table to do experiments, and finally figured out its usage. There is a blog post on the Internet that explains it very well: http://rely1020.blog.ithome.com.tw/post/1606/39111, basically what I want to write is to refer to the blog post, plus a little personal understanding.

To understand PIVOT syntax is to understand why Microsoft designs PIVOT in this way, but I believe it is the real needs that give rise to design ideas, so in the final analysis, we still need to figure out what is "line rotation":

The normal query result is as follows:

Monday 1000

Tuesday 2000

Wednesday 3000

Thursday 4000

Friday 5000

Saturday 6000

Sunday 7000

After the row is transferred to the column, it looks like this:

Monday, Tuesday, Wednesday, Thursday, Friday, Saturday, Sunday

1000 2000 3000 4000 5000 6000 7000

In other words, after the row is transferred to the column, the value of the original column is changed to the name of the column, here is the value of the original WEEK column "Monday", "Tuesday"... the column name is done on the side of "Sunday", and the other thing we need to do is to calculate the values of these columns (the "calculation" here is actually the aggregate function (sum,avg, etc.) in PIVOT)

Now analyze the PIVOT syntax with comments (before you do that, you'd better take a look at the blog post I mentioned above: http://rely1020.blog.ithome.com.tw/post/1606/39111, where the three steps of the PIVOT syntax are important):

SELECT [Monday], [Tuesday], [Wednesday], [Thursday], [Friday], [Saturday], [Sunday]-- here is the third step of PIVOT (selecting the columns of the result set after row conversion), where you can use "*" to select all columns, or you can select only certain columns (that is, certain days) FROM WEEK_INCOME-- here is the second step of PIVOT (preparing the original query results). Because PIVOT converts an original query result set, query a result set first) here it can be a select subquery, but specify an alias for the subquery, otherwise the syntax error PIVOT (SUM (INCOME) for [week] in ([Monday], [Tuesday], [Wednesday], [Thursday], [Friday], [Saturday], [Sunday])-- here is the first step of PIVOT It is also the core of the row-to-column operation. The aggregate function SUM indicates how you need to handle the values of the converted columns, whether they are sum, avg, min,max, and so on. For example, if there are two pieces of data in the week_income table and its week is "Monday", one of them has an income of 1000 and the other income is 500. then using sum here, the value of the "Monday" column after the row is of course 1500. The following for [week] in ([Monday], [Tuesday]...) For [week] means to convert the values of week columns into individual columns, that is, "change columns by value". But there may be a lot of values that need to be converted to columns, and we just want to convert a few of them into columns, so how do we get them? It's in in. For example, I only want to see the working day's income at the moment. In in, I only write "Monday" to "Friday" (note that in contains the value of the original week column, "change the column by value"). In general, the meaning of the sentence SUM (INCOME) for [week] in ([Monday], [Tuesday], [Wednesday], [Thursday], [Friday], [Saturday], [Sunday]) is translated literally, that is, convert the column [week] to "Monday", "Tuesday", "Wednesday", "Thursday", "Friday", "Saturday" and "Sunday" respectively. The values of these columns are the sum of income.) The TBL-- alias must be written.

The above is my understanding of PIVOT, and I try my best to express it. But then again, the personal way of understanding is also different, such as I began to read a lot of blog posts, do not understand the use of PIVOT. The result is still hard through examples and other people's blog posts plus thinking to understand, so if you still can not understand after reading this article, that is very normal, with examples plus their own thinking, slowly will understand.

The above is all the contents of the article "how to use SQL SERVER PIVOT with Line transfer". Thank you for your reading! Hope to share the content to help you, more related knowledge, welcome to follow the industry information channel!

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.