PIVOT and UNPIVOT Syntax - SQL Server

PIVOT Syntax¶

General Form¶

SELECT 
    Aggregate-Value-Label, Pivot-Col-Val1, Pivot-Col-Val2 ... Pivot-Col-ValN
FROM (
    SELECT Pivot-Col, Value-Col
    FROM Source-Table
    ) AS Alias
PIVOT (
    Aggregate-Function(Value-Col)
    FOR Pivot-Col IN (Pivot-Col-Val1, Pivot-Col-Val2 ... Pivot-Col-ValN)
    )

Example 1¶

SQL Statement¶

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

Results of Subquery¶

Results of PIVOT¶

UNPIVOT Syntax¶

General Form¶

SELECT Group-By-Column, Pivot-Column, Value-Column
FROM (
    SELECT Group-By-Column, Pivot-Col1, Pivot-Col2, ... Pivot-ColN
    FROM Pivot-Table
    ) P
UNPIVOT
    (Value-Column FOR Pivot-Column IN 
        (Pivot-Col1, Pivot-Col2, ... Pivot-ColN)
    ) as UNPVT

Example¶

SQL Statement¶

SELECT VendorID, Employee, Orders
FROM 
   (SELECT VendorID, Emp1, Emp2, Emp3, Emp4, Emp5
   FROM pvt) p
UNPIVOT
   (Orders FOR Employee IN 
      (Emp1, Emp2, Emp3, Emp4, Emp5)
)AS unpvt

PIVOT-ed Data¶

Results of UNPIVOT Statement¶