PIVOT and UNPIVOT Query in Sql Server

PIVOT and UNPIVOT Query in Sql Server

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.


UNPIVOT performs the opposite operation to PIVOT by rotating columns of a table-valued expression into column values.

Basic PIVOT Example

The following code example produces a two-column table that has four rows.

USE AdventureWorks2008R2 ;
GO
SELECT DaysToManufacture, AVG(StandardCost) AS AverageCost 
FROM Production.Product
GROUP BY DaysToManufacture;

Here is the result set.



DaysToManufacture          AverageCost

0                          5.0885

1                          223.88

2                          359.10

The following code displays the same result, pivoted so that the DaysToManufacture values become the column headings. A column is provided for three [3] days, even though the results are NULL.










-- Pivot 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
PIVOT
(
AVG(StandardCost)
FOR DaysToManufacture IN ([0], [1], [2], [3], [4])
) AS PivotTable;







Here is the result set.

Cost_Sorted_By_Production_Days    0         1         2           3       4       

AverageCost                       5.0885    223.88    359.1082    NULL    949.4105

http://msdn.microsoft.com/en-us/library/ms177410(v=sql.105).aspx