The unordered keyword displays a row of queried fields.

Use volumes and unblocks

You can use the distinct and undistinct Relational operators to change the table value expression to another table. 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.

Note:
To upgrade a database to SQL Server 2005 or later, you must set the database Compatibility Level to 90 or higher. For information about how to set the database compatibility level, see Sp_dbcmptlevel (Transact-SQL ).

The syntax provided by explain is simpler and more readable than the syntax specified in a series of complex SELECT... CASE statements. For a complete description of the explain syntax, see
FROM (Transact-SQL ).

The following is the annotation syntax.

SELECT <Non-pivot columns>,

[First pivot column] AS <column Name>,

[Second pivot column] AS <column Name>,

...

[Last pivot column] AS <column Name>,

FROM

(<SELECT query for generated data>)

AS <Source Query alias>

Bytes

(

<Aggregate function> (<column to be aggregated>)

FOR

[<Column containing the value of the column title>]

IN ([the first pivot column], [the second pivot column],

... [Last pivot column])

) AS <Pivot table alias>

<Optional order by clause>;

Simple Example

The following code example generates a table with two columns and four rows.

USE AdventureWorks2008R2 ;GOSELECT DaysToManufacture, AVG(StandardCost) AS AverageCost FROM Production.ProductGROUP BY DaysToManufacture;

The following is the result set:

DaysToManufacture AverageCost

0 5.0885

1 223.88

2 359.1082

4 949.4105

There is no product that defines DaysToManufacture as 3.

The following code displays the same result. The result is pivoting to make the DaysToManufacture value a column title. Provide a column to indicate three
[3] days, even if the result is NULL.

-- Pivot table with one row and five columnsSELECT 'AverageCost' AS Cost_Sorted_By_Production_Days, [0], [1], [2], [3], [4]FROM(SELECT DaysToManufacture, StandardCost     FROM Production.Product) AS SourceTablePIVOT(AVG(StandardCost)FOR DaysToManufacture IN ([0], [1], [2], [3], [4])) AS PivotTable;

The following is the result set:

Cost_Sorted_By_Production_Days 0 1 2 3 4

AverageCost 5.0885 223.88 359.1082 NULL 949.4105

Complex example

A common case of using a sheet is that you need to generate a cross-Table report to summarize data. For example, assume that
The AdventureWorks2008R2 sample database queries the PurchaseOrderHeader table to determine the number of purchase orders for certain employees. The following query provides this report (sorted by supplier ).

USE AdventureWorks2008R2;GOSELECT VendorID, [250] AS Emp1, [251] AS Emp2, [256] AS Emp3, [257] AS Emp4, [260] AS Emp5FROM (SELECT PurchaseOrderID, EmployeeID, VendorIDFROM Purchasing.PurchaseOrderHeader) pPIVOT(COUNT (PurchaseOrderID)FOR EmployeeID IN( [250], [251], [256], [257], [260] )) AS pvtORDER BY pvt.VendorID;

Some result sets are as follows.

VendorID Emp1 Emp2 Emp3 Emp4 Emp5

1492 2 5 4 4 4

1494 2 5 4 5 4

1496 2 4 4 5

1498 2 5 4 4 4

1500 3 4 4 5 4

The results returned by this nested select statement will be viewed in the EmployeeID column.

SELECT PurchaseOrderID, EmployeeID, VendorIDFROM PurchaseOrderHeader;

This means that the unique value returned by the EmployeeID column is automatically changed to the field in the final result set. Therefore, each
Each employee ID has a corresponding column: In this example, employees 164, 198, 223, 231, and
233. As a value column, the PurchaseOrderID column groups the columns (called group columns) returned in the final output. In this example
The COUNT function aggregates grouping columns. Note that a warning message is displayed, indicating that each employee calculates
COUNT does not consider any null values in the PurchaseOrderID column.

Important
If the aggregate function and aggregate function are used together, any null values in the value column are not considered during aggregation calculation.

Unregister converts a column to a row by performing almost the opposite operation as the condition. Assume that the table generated in the preceding example is stored as pvt in the database, and you need to set the column identifier
Emp1, Emp2, Emp3, Emp4, and
Emp5 is rotated to the row value corresponding to a specific supplier. This means that the other two columns must be identified. Columns that contain the column values (Emp1, emp2...) to be rotated are called
The column that stores the value of the column to be rotated is called Orders. These columns correspond
Pivot_column and value_column. The query is as follows.

--Create the table and insert values as portrayed in the previous example.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

Some result sets are as follows.

VendorID Employee Orders

--------------------------

1 Emp1 4

1 Emp2 3

1 Emp3 5

1 Emp4 4

1 Emp5 4

2 Emp1 4

2 Emp2 1

2 Emp3 5

2 Emp4 5

2 Emp5 5

...

Please note that uninitiated operations are not completely reverse operations of the operator. Merge performs an aggregation operation to merge multiple possible rows into a single row in the output. Unmerged does not reproduce the results of the original table value expression, because the rows have been merged. In addition, the null value in the unordered input is not displayed in the output, but the input may have the original null value before the unordered operation is executed.

The Sales. vSalesPersonSalesByFiscalYears view in the example database of AdventureWorks2008R2 returns the total Sales of each salesperson for each fiscal year using week. To write a View Script in SQL Server Management Studio, find the view corresponding to the AdventureWorks2008R2 database in "Object Resource Manager" under the "View" folder. Right-click the view name and select "Write View Script ".