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:: The database compatibility level must be set to 90 when the slave and unsecure databases are upgraded to Microsoft SQL Server 2005.

The syntax provided by explain is simpler and more readable than the syntax specified in a series of complex SELECT... CASE statements.
The following is the annotation syntax.

SELECT<Non-converted Ted column>,

[First converted Ted column]AS<Column name>,

[Second specified Ted column] AS <Column name>,

...

[Last modified Ted column]AS<Column name>

FROM

(<SELECT query that produces the data>)

AS<Alias for the source query>

Bytes

(

<Aggregation function> (<Column being aggregated>)

FOR

[<Column that contains the values that will become column headers>]

IN ([First converted Ted column],[Second specified Ted column],

...[Last modified Ted column])

) AS<Alias for the specified table>

<Optional order by clause>

SELECT <non-converted Ted column>,

[First converted Ted column] AS <column name>,

[Second specified Ted column] AS <column name>,

...

[Last modified Ted column] AS <column name>

FROM

(<SELECT query that produces the data>)

 AS <alias for the source query>

Bytes

(

<Aggregation function> (<column being aggregated>)

FOR

[<Column that contains the values that will become column headers>]

IN ([first converted Ted column], [second converted Ted column],

... [Last modified Ted column])

) AS <alias for the specified table>

<Optional order by clause>

The following sample code generates a table with two columns and four rows.
USE AdventureWorks;
GO
SELECT DaysToManufacture, AVG (StandardCost) AS AverageCost
FROM Production. Product
Group by DaysToManufacture

The following is the result set:

DaysToManufacture AverageCost

0 5.0885

1 223.88

2 359.1082

4 949.4105

Not DefinedDaysToManufactureIs 3 products.

The following code displays the same result. The result is pivoting to makeDaysToManufactureValue becomes the column title. Provide a column to indicate three[3]Day, even if the result isNULL.

-- Partition table with one row and five columns
SELECT 'averagecost' AS Cost_Sorted_By_Production_Days,
[0], [1], [2], [3], [4]
FROM
(SELECT DaysToManufacture, StandardCost
FROM Production. Product) AS SourceTable
Bytes
(
AVG (StandardCost)
FOR DaysToManufacture IN ([0], [1], [2], [3], [4])
) AS portable tTable

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

May be usedBytesIs: generate a cross-tabulation report to summarize data. For example, assume thatAdventureWorksQuery in the sample databasePurchaseOrderHeaderTable to determine the number of purchase orders under certain specific employees. The following query provides this report (sorted by supplier ).

USE AdventureWorks;
GO
SELECT VendorID, [164] AS Emp1, [198] AS Emp2, [223] AS Emp3, [231] AS Emp4, [233] AS Emp5
FROM
(SELECT PurchaseOrderID, EmployeeID, VendorID
FROM Purchasing. PurchaseOrderHeader) p
Bytes
(
COUNT (PurchaseOrderID)
FOR EmployeeID IN
([164], [198], [223], [231], [233])
) AS pvt
Order by VendorID

Some result sets are as follows.

VendorID Emp1 Emp2 Emp3 Emp4 Emp5
1 4 3 5 4 4
2 4 1 5 5
3 4 3 5 4 4
4 4 2 5 4
5 5 1 5 5 5

InEmployeeIDPivot results returned by this nested select statement.

SELECT PurchaseOrderID, EmployeeID, VendorID
FROM PurchaseOrderHeader

This meansEmployeeIDThe unique value returned by the column is automatically converted into a field in the final result set. Result:EmployeeIDEach number has a corresponding column: In this example, it is an employee164,198,223,231And233.PurchaseOrderIDAs a value column, the columns (called grouping columns) returned in the final output are grouped based on this column. In this exampleCOUNTFunction aggregate grouping column. Note that a warning message is displayed, indicating that each employee calculatesCOUNTNot Displayed inPurchaseOrderIDAny null value in the 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 storedPvtAnd you need to set the column identifierEmp1,Emp2,Emp3,Emp4AndEmp5Rotate to the row value corresponding to a specific supplier. This means that the other two columns must be identified. Contains the column values to be rotated (Emp1,Emp2...) Is calledEmployeeThe column that stores the value of the current column to be rotated is calledOrders. These columns correspondEffect_columnAndValue_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)
GO
Insert into pvt VALUES)
Insert into pvt VALUES)
Insert into pvt VALUES (3, 4, 3, 5, 4)
Insert into pvt VALUES)
Insert into pvt VALUES)
GO
-- Unregister the table.
SELECT VendorID, Employee, Orders
FROM
(SELECT VendorID, Emp1, Emp2, Emp3, Emp4, Emp5
FROM pvt) p
Unregister
(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.

AdventureWorksIn the example databaseSales. vSalesPersonSalesByFiscalYearsThe view returns the total sales of each salesperson in each fiscal year using the histogram. To write a View Script in SQL Server Management Studio, go"Object Resource Manager"In"View"FolderAdventureWorksView corresponding to the database. Right-click the view name and select"Write View Script".