SQL built-in function pivot powerful row to column function

http://blog.csdn.net/xb12369/article/details/8149608

Http://www.cnblogs.com/lwhkdash/archive/2012/06/26/2562979.html

Row to column actually means that, after row to column, the value of the original column has been changed to the column name

Original table

Row-to-column statement:

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 of the place, Perform row-to-column operations. 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

Final result

SQL built-in function pivot powerful row to column function