UNPIVOT Works Miracle on Tabular Imports
By Kalman Toth, M.Phil., M.Phil., MCDBA, MCITP

July 23, 2008

SQL Server 2005 has a new SELECT clause: UNPIVOT. It rotates horizontally laid out data into vertical layout. That is, of course, the preferred way for SQL database engine processing.

For reporting purposes the vertically laid out SQL results frequently tabularized into horizontal layout, crosstab reports. They are easier to comprehend and analyze.

It frequently happens that data received into staging tables has tabular layout because they originate in reporting systems. The data can be UNPIVOTed with a series of INSERT...SELECT... statements. UNPIVOT will do it in one statement.

The following example shows how UNPIVOT rotates the horizontal data into vertical format. The data is the number of calls taken by customer service representatives from customers. For readable report the foreign key references require joins to the primary key tables to translate the ID-s into names.

USE tempdb;

GO

CREATE TABLE CustomerSupportLog (CustomerID int, CSID1 int, CSID2 int,

CSID3 int, CSID4 int, CSID5 int)

GO

INSERT INTO CustomerSupportLog VALUES (7,4,3,3,4,4)

INSERT INTO CustomerSupportLog VALUES (52,4,1,5,5,5)

INSERT INTO CustomerSupportLog VALUES (33,4,3,5,4,4)

INSERT INTO CustomerSupportLog VALUES (4,4,2,5,8,4)

INSERT INTO CustomerSupportLog VALUES (65,5,1,8,5,5)

GO

select * from CustomerSupportLog order by CustomerID

go

-- UNPIVOT the table.

SELECT CustomerID, CSName, Calls

FROM

(SELECT CustomerID, CSID1, CSID2, CSID3, CSID4, CSID5

FROM CustomerSupportLog) p

UNPIVOT

(Calls FOR CSName IN

(CSID1, CSID2, CSID3, CSID4, CSID5)

)AS unCustomerSupportLog

order by CustomerID, CSName

GO

-- drop table CustomerSupportLog

The result of the regular SELECT:

CustomerID  CSID1 CSID2 CSID3 CSID4 CSID5

4     4     2     5     8     4

7     4     3     3     4     4

33    4     3     5     4     4

52    4     1     5     5     5

65    5     1     8     5     5

The partial result of the SELECT with UNPIVOT:

CustomerID  CSName      Calls

4     CSID1 4

4     CSID2 2

4     CSID3 5

4     CSID4 8

4     CSID5 4

7     CSID1 4

7     CSID2 3

7     CSID3 3

7     CSID4 4

7     CSID5 4

33    CSID1 4