An illustrative example of the row and column transfers in SQL Server

These days in a bidding system in the approval module, which about the quotation information this piece, using pivot and UNPIVOT to achieve the data of the row and column, the following brief introduction, the actual case, easy to recall and record the conditions used under the circumstances. The pivot and UNPIVOT functions are the new 2 functions provided by SQL2005, PIVOT rotates table-valued expressions by converting unique values in one column of an expression to multiple columns in the output, and performs aggregations if necessary for any remaining column values that are required in the final output. UNPIVOT performs the reverse operation with PIVOT, converting columns of table-valued expressions to column values.

Let me illustrate the use of the entire function using pivot:

Syntax :

SELECT < non-perspective columns;

[First pivot column] as < column name;

[Second pivot column] as < column name;

[Last Pivot column] as < column name;

From (< SELECT query for data generation >)

Aliases for as < source queries >

PIVOT (

< aggregate functions > (< Columns to aggregate >)

For

[< The column that contains the value to be the column header;]

In ([First pivot column], [Second pivot column], ... [last pivot column])

) as < pivot table Aliases >

Example :
Select Price,sup_name,quot_item1,quot_item3,quantity from
Q2b_quot_item where < related conditions filter >

perform the display :

Price Sup_name quot_item1 quot_item3 QUANTITY
342 test supplier 1 5h52921000088 Scissors 4.000
422 Test supplier 1 5h48911000023 Black fountain refill 2.000
211 Test supplier 1 5h57161000002 Calculator 2.000
324 Test supplier 2 5h52921000088 Scissors 4.000
342 test supplier 2 5h48911000023 Black fountain refill 2.000
234 Test supplier 2 5h57161000002 Calculator 2.000
434 Test supplier 3 5h52921000088 Scissors 4.000
232 Test supplier 3 5h48911000023 Black Fountain refill 2.000
2432 Test supplier 3 5h57161000002 Calculator 2.000

It is found that the read data is normally displayed as a column value according to Sup_name (vendor). At present, the customer requests to the material as the conditions for the supply of quotations to summarize the display. Next we use pivot to implement the column change according to the syntax we just provided.

SQL: select* from (select Price,sup_name,quot_item1,quot_item3,quantity from
Q2b_quot_item where < related conditions filter > as < aliases >

Pivot (max (price) for Ord. Sup_name in (test supplier 1, test supplier 3, test supplier 2)) b

Show: quot_item1 quot_item3 QUANTITY test supplier 1 Test supplier 3 Test supplier 2
5h48911000023 Black Fountain refill 2.000 422 232 342
5h52921000088 Scissors 4.000 342 434 324
5h57161000002 Calculator 2.000 211 2432 234

The use of pivot is a simple implementation of the column change, for similar data processing gray often practical, avoid the use of case or circular cursor complex processing, greatly improve the processing speed and code neat and elegant.

Precautions : 1. When you use PIVOT and UNPIVOT for a database that is upgraded to SQL Server 2005 or later, you must set the compatibility level of the database to 90 or higher;

2.UNPIVOT will perform almost the opposite of the pivot, converting columns to rows, but not exactly the same, and Pivot performs an aggregation, merging multiple possible rows into a single row in the output. UNPIVOT does not reproduce the result of the original table-valued expression because the rows have been merged. In addition, null values in the input of the UNPIVOT are not displayed in the output, and the input may have the original null value before performing a PIVOT operation.

3. The difference between dynamic processing and static processing is the number of career changes. That is, for ... in contains data.

Finally, simply write:Unpivot is used to convert column names to column values (that is, column change)

Grammar:

UNPIVOT (

Value_column

For Pivot_column

In (<column_list>)

)

A simple test, and then there are practical applications to copy the actual examples. Not to go further into the discussion.

An illustrative example of the row and column transfers in SQL Server