How to UNPIVOT Data Using T-SQL

Jonathan Kehayias, April 8, 2008

A common expectation in data extraction is the ability to transform the
output of multiple rows into multiple columns in a single row, or pivoting the data,
but what if you find yourself in the position of needing just the opposite, rows
of data turned back into columns? SQL Server 2005/2008 provide the ability
to do this with the UNPIVOT operator in a Query.

For example, from the following data:

orderid	Gift Card	T-Shirt	Shipping
1	2	NULL	1
2	2	2	2

The desired output is:

orderid	productname	productqty
1	Gift Card	2
1	Shipping	1
2	Gift Card	2
2	T-Shirt	2
2	Shipping	2

There are several methods to accomplish the desired output.
This demonstration provides a UNPIVOT Solution for SQL Server 2005 / SQL Server 2008 ,
as well as a T-SQL Solution for SQL Server 2000

Create Sample Data

-- Suppress data loading messages
SET NOCOUNT ON
 
-- Create Sample Data using a Table Varable
DECLARE @Orders TABLE
(orderid int, GiftCard int, TShirt int, Shipping int)
-- Load Sample Data
INSERT INTO @Orders VALUES (1, 2, NULL, 1)
INSERT INTO @Orders VALUES (2, 2, 2, 2)
 
 

Return to Top

SQL Server 2005 / SQL Server 2008 Solution

--Query to Retrieve Desired Data
SELECT OrderID, convert(varchar(15), ProductName) [ProductName], ProductQty
FROM
(SELECT OrderID, GiftCard, TShirt, Shipping
 FROM @Orders) p
UNPIVOT
(ProductQty FOR ProductName IN
	([GiftCard], [TShirt], [Shipping])
) as unpvt
 
 
--Results from Query
OrderID     ProductName     ProductQty
----------- --------------- -----------
1           GiftCard        2
1           Shipping        1
2           GiftCard        2
2           TShirt          2
2           Shipping        2

Return to Top

SQL 2000 Solutions

SELECT OrderID, 'GiftCard' [ProductName], GiftCard [ProductQty]
FROM @Orders
WHERE GiftCard IS NOT NULL
UNION
SELECT OrderID, 'TShirt', TShirt
FROM @Orders
WHERE TShirt IS NOT NULL
UNION
SELECT OrderID, 'Shipping', Shipping
FROM @Orders
WHERE Shipping IS NOT NULL
 
--Results from Query
OrderID     ProductName ProductQty
----------- ----------- -----------
1           GiftCard    2
1           Shipping    1
2           GiftCard    2
2           Shipping    2
2           TShirt      2

Return to Top

To learn how to PIVOT data in a query, please see PIVOT Data Using T-SQL.

To find out more about how to use UNPIVOT in SQL 2005/2008, see: PIVOT Data Using T-SQL.

http://msdn2.microsoft.com/en-us/library/ms177410.aspx

___________________________________________________________________________________________________________________
Page Created By: Jonathan Kehayias, Apr 8, 2008