Pivot and unpivot data: PIVOT and UNPIVOT operator

One of the new features that SQL server 2005 has is PIVOT and UNPIVOT operator.

<pivot_clause> ::=

        ( aggregate_function ( value_column )

        FOR pivot_column

        IN ( <column_list> )

 

<unpivot_clause> ::=
        ( value_column FOR pivot_column
  IN ( <column_list> ) )

 

E.g:

USE AdventureWorks;

GO

DECLARE @pvt TABLE (VendorID int NOT NULL, Emp1 int NOT NULL, Emp2 int NOT NULL,
     Emp3 int NOT NULL, Emp4 int NOT NULL, Emp5 int NOT NULL)

— PIVOT data and save in the table variable
INSERT @pvt (VendorID, Emp1, Emp2, Emp3, Emp4, Emp5)
SELECT VendorID, [164] AS Emp1, [198] AS Emp2, [223] AS Emp3, [231] AS Emp4, [233] AS Emp5
FROM
(SELECT PurchaseOrderID, EmployeeID, VendorID
FROM Purchasing.PurchaseOrderHeader WHERE VendorID<10) p
PIVOT
(
COUNT (PurchaseOrderID)
FOR EmployeeID IN
( [164], [198], [223], [231], [233] )
) AS pvt

 

— Pivot results

SELECT * FROM @pvt ORDER BY VendorID

 

–Unpivot the table.
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

You can see, the pivoted column list must be in the IN expression, no subquery is allowed. If you use the PIVOT in a stored procedure, the column list in most cases is determined according to its parameters. We have to resort to dynamic T-SQL again to build the IN list and the PIVOT clause. The following stored procedure pivot the purchase order for each employee managed by any manager, e.g.:

EXEC dbo.GetOrderNumbers @ManagerID=274
GO
EXEC dbo.GetOrderNumbers @ManagerID=2
GO
EXEC dbo.GetOrderNumbers @ManagerID=16
GO

 

SET ANSI_NULLS ON
GO
CREATE  PROCEDURE dbo.GetOrderNumbers @ManagerID int
AS
— Pivot orders for each employee managed by the mamnager
DECLARE @SQL nvarchar(max)
DECLARE @PvtList nvarchar(max)
DECLARE @ColList nvarchar(max)

SELECT @PvtList=N”,@ColList=N”
SELECT @PvtList = @PvtList+N’,[‘+CAST(EmployeeID as nvarchar(20))+N’]’,@ColList = @ColList+N’,[‘+CAST(EmployeeID as nvarchar(20))+N’] AS [Emp’+CAST(EmployeeID AS nvarchar(22))+N’]’
FROM HumanResources.Employee
WHERE ManagerID=@ManagerID
IF @PvtList=N”
BEGIN
 PRINT ‘The manager does not have employee’
 RETURN 1
END
SET @PvtList=RIGHT(@PvtList,LEN(@PvtList) – 1 )
SET @ColList=RIGHT(@ColList,LEN(@ColList) – 1 )

SET @SQL=N’SELECT VendorID,’+@ColList+N’
FROM (SELECT PurchaseOrderID, A.EmployeeID, VendorID
 FROM Purchasing.PurchaseOrderHeader A, HumanResources.Employee B
 WHERE A.EmployeeID=B.EmployeeID AND B.ManagerID=’+CAST(@ManagerID AS nvarchar(22))+’) RawData
PIVOT
(
COUNT(PurchaseOrderID)
FOR EmployeeID IN
( ‘+@PvtList+’)
) AS pvt
ORDER BY VendorID’

PRINT @SQL
EXEC (@SQL)
GO

 

 

 

Advertisements
This entry was posted in SQL server 2005. Bookmark the permalink.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s