Search Wiki:

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
Last edited Apr 28 2008 at 2:02 AM  by ArnieRowland, version 7
Comments
Zubair wrote  Jul 10 2009 at 7:29 AM  
Hi,
You provide a good example to UNPIVOT the data. but i have a different scenario.
If All columns (GiftCard, TShirt, Shipping) have different data types,
then how can do similar UNPIVOT logic to get efficient and accurate results?
I need your comments and suggestions.

Thanks

Regards,
Zubair

malikcin wrote  Dec 22 2010 at 7:10 PM  
You can CAST your columns to the same data type in your Select Statement i.e.
SELECT OrderID, convert(varchar(15), ProductName) [ProductName], ProductQty
FROM
(SELECT OrderID, CAST(GiftCard as varchar(50)) AS [GiftCard], CAST(TShirt as varchar (50)) AS [TShirt] ............... etc.

rrozema wrote  Nov 15 2012 at 7:34 PM  
A much more efficient way to unpivot your data is available from SQL server 2005 by a combination of cross apply and union all. This also provides us with much more control over what is returned under which conditions than using unpivot. Plus I think the syntax is much better readable. An example returning the exact same results as the first unpivot example. See for yourself the performance differences:

select o.orderid, x.ProductName, x.ProductQty
from @Orders o
cross apply (
select convert(varchar(15), 'GiftCard'), o.GiftCard where o.GiftCard is not null
union all
select 'TShirt', o.TShirt where o.TShirt is not null
union all
select 'Shipping', o.Shipping where o.Shipping is not null
) x ( ProductName, ProductQty);

rrozema wrote  Nov 15 2012 at 7:40 PM  
Oh and almost forgot this one: it also gives you the freedom to choose the values returned for the product name, instead of being forced to return the column names.

Updating...
Page view tracker