SQL Server PIVOT and UNPIVOT Tutorial: Syntax and Practical Examples

Summary: Master the SQL Server PIVOT and UNPIVOT operators. This guide provides the general syntax and real-world examples using AdventureWorks to transform rows into columns and back again.

SQL Server PIVOT and UNPIVOT: Transforming Data

The PIVOT operator in SQL Server is a powerful tool for data analysis. It allows you to rotate a table-valued expression by turning unique values from one column into multiple columns in the output, while performing aggregations on any remaining column values.

---

1. General PIVOT Syntax

Understanding the structure is key to mastering pivot operations. The query requires a source subquery, an aggregate function, and an IN clause for the new headers.

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>;
        

2. Basic PIVOT Example

This example calculates the average cost of products, sorted by the number of days it takes to manufacture them.

-- 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;
        

3. Advanced PIVOT Query

Using the AdventureWorks database to count purchase orders per employee, pivoted by specific Employee IDs.

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;
        

4. The UNPIVOT Operation

The UNPIVOT operator performs the opposite operation, transforming columns back into rows for normalized reporting.

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

INSERT INTO #pvt VALUES (1,4,3,5,4,4), (2,4,1,5,5,5), (3,4,3,5,4,4);  

-- 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
        

---

Need Dynamic Columns? Standard PIVOT requires hardcoded values in the IN clause. For variable headers, you'll need to use Dynamic SQL to build the column list at runtime.