Mitul Bahl

• Sep 24th, 2007

 

The PIVOT operator rotates rows into columns, optionally performing aggregations or other mathematical calculations along the way. It widens the input table expression based on a given pivot column and generates an output table with a column for each unique value in the pivot column. The UNPIVOT operator performs an operation opposite to the one PIVOT performs; it rotates columns into rows. The UNPIVOT operator narrows the input table expression based on a pivot column.

For eg:

SELECT
       CAST(YEAR(SaleDate) AS VARCHAR(4)), BigScreen, PoolTable, Computer
 FROM
       SalesHistory
 PIVOT
 
 

(
       SUM(SalePrice) FOR Product IN(BigScreen, PoolTable, Computer)
 
 

) AS p

I too don't have used this operator yet. But have read in the articles and tried to provide a solution to your answer. Hope this might help you out.

neel_desai

 Profile Answers by neel_desai 

• Oct 11th, 2008

 

You can use the PIVOT relational operators to change a table-valued expression into another table. PIVOT rotates a table-valued expression by turning the unique values from one column in the expression into multiple columns in the output, and performs aggregations where they are required on any remaining column values that are wanted in the final output.

The syntax for PIVOT provides is simpler and more readable than the syntax that may otherwise be specified in a complex series of SELECT...CASE statements.

EXAMPLE:
USE AdventureWorks ;

SELECT 'AverageCost' AS Cost_Sorted_By_Production_Days, 
[0], [1], [2], [3], [4]
FROM
(SELECT DaysToManufacture, StandardCost 
    FROM Production.Product) AS SourceTable
PIVOT
(
AVG(StandardCost)
FOR DaysToManufacture IN ([0], [1], [2], [3], [4])
) AS PivotTable

OUTPUT:

Cost_Sorted_By_Production_Days    0         1         2           3       4       

AverageCost                       5.0885    223.88    359.1082    NULL    949.4105