Pivot table

Below is the general syntax used when working with the PIVOT operator in SQL. This technique transforms rows into columns to better analyze aggregated data.

SELECT <non-pivoted column>,  
    [first pivoted column] AS <column name>,  
    [second pivoted column] AS <column name>,  
    ...  
    [last pivoted column] AS <column name>  
FROM  
    (<SELECT query that retrieves the data>) AS <source alias>  
PIVOT  
(  
    <aggregate function>(<column to aggregate>)  
    FOR [<column with values to become headers>]  
    IN ([first pivoted column], [second pivoted column], ..., [last pivoted column])  
) AS <pivot table alias>  
<optional ORDER BY clause>;

Example: Basic PIVOT Query

-- This example creates a 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;

Advanced PIVOT Example

USE AdventureWorks2014;  
GO  
SELECT VendorID, [250] AS Emp1, [251] AS Emp2, [256] AS Emp3, [257] AS Emp4, [260] AS Emp5  
FROM  
(SELECT PurchaseOrderID, EmployeeID, VendorID FROM Purchasing.PurchaseOrderHeader) AS p  
PIVOT  
(  
COUNT(PurchaseOrderID)  
FOR EmployeeID IN ([250], [251], [256], [257], [260])  
) AS pvt  
ORDER BY pvt.VendorID;

Example: UNPIVOT Operation

-- Creating the base table and inserting sample values
CREATE TABLE pvt (
    VendorID INT, 
    Emp1 INT, 
    Emp2 INT, 
    Emp3 INT, 
    Emp4 INT, 
    Emp5 INT
);  
GO  

INSERT 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  

-- Applying the UNPIVOT operator
SELECT VendorID, Employee, Orders  
FROM  
(SELECT VendorID, Emp1, Emp2, Emp3, Emp4, Emp5 FROM pvt) AS p  
UNPIVOT  
(Orders FOR Employee IN (Emp1, Emp2, Emp3, Emp4, Emp5)) AS unpvt;  
GO