Jasinski Technical Wiki

Navigation

Home Page
Index
All Pages

Quick Search
»
Advanced Search »

Contributor Links

Create a new Page
Administration
File Management
Login/Logout
Your Profile

Other Wiki Sections

Software

PoweredBy

PIVOT and UNPIVOT Syntax - SQL Server

RSS
Modified on Thu, May 28, 2009, 8:05 AM by Administrator Categorized as 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

DaysToManufacture StandardCost
05.0885
1223.88
2359.1082
4949.4105

Results of PIVOT

Cost_Sorted_By_Production_Days01234
AverageCost5.0885223.88359.1082NULL949.4105

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

VendorIDEmp1Emp2Emp3Emp4Emp5
143544
241555
343544
442554
551555

Results of UNPIVOT Statement

VendorIDEmployeeOrders
1Emp14
1Emp23
1Emp3 5
1Emp44
1Emp54
2Emp14
2Emp21
2Emp35
2Emp45
2Emp55

ScrewTurn Wiki version 3.0.1.400. Some of the icons created by FamFamFam. Except where noted, all contents Copyright © 1999-2017, Patrick Jasinski.