In-depth explanation of batch row-column conversion in SQL, sqlbench

In-depth explanation of batch row-column conversion in SQL, sqlbench

Rotate rotate the table value expression by converting the unique value in a column of the expression to multiple columns in the output, and aggregate the values of any other columns required in the final output if necessary. Unregister and distinct perform the opposite operation to convert the columns in the Table value expression to column values.

To put it simply, rows are converted to columns, while unrows are columns.

I. Producer instance

1. Create a table

Create a sales table. The year field indicates the year, the quarter field indicates the quarter, and the amount field indicates the sales. The quarter Fields use Q1, Q2, Q3, and Q4 to represent the first, second, third, and fourth quarter respectively.

Create table SalesByQuarter (year INT, -- year quarter CHAR (2), -- quarterly amount MONEY -- total)

2. Fill in the table data

Use the following program to fill in the table data.

SET NOCOUNT ON DECLARE @index INT DECLARE @q INT SET @index = 0 DECLARE @year INT while (@index < 30) BEGIN  SET @year = 2005 + (@index % 4)  SET @q = (CAST((RAND() * 500) AS INT) % 4) + 1  INSERT INTO SalesByQuarter VALUES (@year, 'Q' + CAST(@q AS CHAR(1)), RAND() * 10000.00)  SET @index = @index + 1

3. What should we do if we want to compare the sales status of each quarter of each year? There are two methods:

(1) query using the CASE statement of the traditional Select statement

In versions earlier than SQL Server, converting row-level data to column-level data requires a series of CASE statements and aggregate queries. Although this method enables developers to have high control over the returned data, it is very troublesome to compile and write these queries.

SELECT year as year, sum (case when quarter = 'q1' then amount else 0 end) quarter, sum (case when quarter = 'q2 'then amount else 0 end) quarter 2, sum (case when quarter = 'q3 'then amount else 0 end) Three quarter, sum (case when quarter = 'q4' then amount else 0 end) FROM SalesByQuarter group by year order by year DESC

The result is as follows:

(2) Use Lifecycle

Since SQL Server 2005 has a new distinct operator, CASE statements and GROUP BY statements are no longer required. (Each distinct query involves some type of aggregation, so you can ignore the group by statement .) The explain operator allows us to use CASE statement queries to implement the same functions, but you can use less code to implement it and it looks more beautiful.

SELECT year as year, Q1 as quarter, Q2 as quarter 2, Q3 as quarter 3, Q4 as quarter FROM SalesByQuarter quarter (SUM (amount) FOR quarter IN (Q1, Q2, Q3, q4) AS P ORDER BY YEAR DESC

The result is as follows:

2. Use the following example to describe the renewal process in detail.

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

3. unregister

Obviously, the UN prefix indicates that the operations it performs are the opposite of the limit, that is, column-to-row operations. Unregister operations involve the following three logical processing stages.

1. Generate a copy
2. Extract Elements
3. Delete rows with NULL

Unordered instance

CREATE TABLE pvt (VendorID int, Emp1 int, Emp2 int, Emp3 int, Emp4 int, Emp5 int);GOINSERT INTO pvt VALUES (1,4,3,5,4,4);INSERT INTO pvt VALUES (2,4,1,5,5,5);INSERT INTO pvt VALUES (3,4,3,5,4,4);INSERT INTO pvt VALUES (4,4,2,5,5,4);INSERT INTO pvt VALUES (5,5,1,5,5,5);GO--Unpivot the table.SELECT VendorID, Employee, OrdersFROM  (SELECT VendorID, Emp1, Emp2, Emp3, Emp4, Emp5 FROM pvt) pUNPIVOT (Orders FOR Employee IN   (Emp1, Emp2, Emp3, Emp4, Emp5))AS unpvt;GO

Analysis of the above unordered instance

Unregister is input to the left table Expression P. The first step is to generate multiple copies for the row in P. A copy is generated for each column in unregister. Because the IN clause has five column names, five copies must be generated for each source row. A new column is added to the virtual table, which is used to save the name of the source column IN string format (for between and IN, the preceding example is "Employee ). Step 2: extract the row corresponding to the column name from the source column based on the value in the newly added column. Step 3: delete the row whose result column value is null to complete the query.