Row-to-column: SQL Server alias and usage explanation

In database operations, we sometimes need to implement "Row-to-column" requirements. For example, the following table shows the weekly income table of a store:

WEEK_INCOME(WEEK VARCHAR(10),INCOME DECIMAL)

Insert some simulated data first:

Insert into week_income select 'monday', 1000 Union allselect 'tues', 2000 Union allselect 'wedday', 3000 Union allselect 'thurs', 4000 Union allselect 'Friday ', 5000 Union allselect 'saturday', 6000 Union allselect 'sunday', 7000

 

Generally, the most frequently used query is to query the daily or daily income of a week. For example, to query the total income from Monday to Sunday:

SELECT WEEK,INCOME FROM WEEK_INCOME

Obtain the following query result set:

Week income
Mon 1000
Tuesday 2000
Wednesday 3000
Thursday 4000
Friday 5000
Sat 6000
Sunday 7000

 

However, in some cases (usually in some reports), we want to display the income from Monday to Sunday in one row. The query result set should be like this:

Monday Tuesday Thursday Saturday
1000 2000 3000 4000 5000 6000 7000

In this case, the SQL query statement can be written as follows:

Select sum (Case week when 'then income end) as [Monday], sum (Case week when 'tuesday' then income end) as [Tuesday], sum (Case week when 'then income end) as [Wednesday], sum (Case week when '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, SQL Server 2005 provides a simpler method, which is the "operator" relational operator. (On the contrary, "column-to-row" is uncommitted.) The following is an SQL statement that implements "Row-to-column" using limit.

Select [Monday], [Tuesday], [Wednesday], [Thursday], [Friday], [Saturday] From week_incomepivot (sum (income) for [week] In ([Monday], [Tuesday], [Wednesday], [Thursday], [Friday], [Saturday], [Sunday]) TBL

 

 

Refer to the usage of tracing in msdn:

Http://technet.microsoft.com/zh-cn/library/ms177410 (V = SQL .105). aspx

 

However, the description on msdn is too formal and serious. I haven't figured out how to use explain for a long time, but I don't know the meaning of the syntax in explain. As a result, Google made a lot of materials and made a test through the week_income table example mentioned above, and finally figured out its usage. There is a blog on the Internet to explain very well: T-SQL render law analysis and practice, basically I want to write is to refer to the blog, coupled with my own personal understanding.

To understand the explain syntax, we need to understand why Microsoft designed the explain statement in this way. But I believe that the actual demand gave birth to the design idea. In the final analysis, we still need to figure out what is "Row-to-column ":

Normally, the query result is as follows:

Mon 1000
Tuesday 2000
Wednesday 3000
Thursday 4000
Friday 5000
Sat 6000
Sunday 7000

This is the case after the row-to-column conversion:

Monday Tuesday Thursday Saturday
1000 2000 3000 4000 5000 6000 7000

That is to say, after a row is converted into a column, the value of an original column is changed into a column name, where the original week column value is "Monday", "Tuesday "... "Sunday" is used as the column name, and another task we need to do is to calculate the values of these columns (here the "calculation" is actually the aggregate function (sum, AVG ))

Now let's take a look at the explain syntax with comments (before that, it's better to look at the blog post I mentioned above: the three steps of the explain syntax mentioned in the T-SQL compiler syntax are very important):

Select [Monday], [Tuesday], [Wednesday], [Thursday], [Friday], [Saturday], [Sunday] -- this is the third step of "Week" (select the result set column after the row is converted to the column). Here, you can use "*" to select all columns, you can also select only some columns (that is, some days) from week_income -- here is the second step of prepare (prepare the original query results, because explain is a conversion operation on the original query result set, a result set is queried first.) Here it can be a select subquery, but an alias must be specified for the subquery, otherwise, the syntax is incorrect (sum (income) for [week] In ([Monday], [Tuesday], [Wednesday], [Thursday], [Friday], [Saturday], [Sunday]) -- this is the first step of the primary node, and also the core part. You can perform row-to-Column Operations. The aggregate function Sum indicates how to process the converted column values, such as sum, AVG, Min, and Max. For example, if the week_income table contains two pieces of data and its week is "Monday", one of which has an income value of 1000 and the other has an income value of 500, sum is used here, the value of the "Monday" column after the row-to-column conversion is of course 1500. For [week] In ([Monday], [Tuesday]...) for [week] means to convert the values of the week column into one column, that is, "change by value ". However, there may be many values to be converted into columns. We only want to convert several of them into columns. How can we get them? It is in. For example, I only want to look at the income of working days at the moment, and only write "Monday" to "Friday" in. (Note that in is the value of the original week column, "Change columns by value "). In general, sum (income) for [week] In ([Monday], [Tuesday], [Wednesday], [Thursday], [Friday], [Saturday], [Sunday]) if the meaning of this sentence is literally translated, that is, the value of [week] In the column is "Monday", "Tuesday", "Week", "Thursday ", "Friday", "Saturday", and "Sunday" are converted into columns respectively. The values of these columns are the sum of income .) TBL -- alias must be written

 

The above is my understanding of NLP and I try my best to express it. However, my personal understanding is also different, just as I have read many blog posts and have not figured out the usage of NLP. The result is hard to understand through examples and other blog posts plus thinking. So if you still cannot understand this article, it is normal to use it with examples and add your own thoughts, it is easy to understand.