T-SQL Pivots a Table

This post demos how to pivot a table using both the Pivot function and a home made approach. The code can be found from GIST.

Problem
An OrderItem table contains orderId, itemName and qty as its columns. Now we want to see the total ordered quantity for each product across the orders.
OrderItem

Solution #1: Home-Made pivot table
This solution is to use the case statement in the select clause to transform the original table. Code is

select sum(ipadQty) as ipadTotalQty, sum(iphoneQty) as iphoneTotalQty, sum(ipodQty) ipodTotalQty
from
(
	select 
		ipadQty=case when itemName='ipad' then qty else 0 end,
		iphoneQty=case when itemName='iphone' then qty else 0 end,
		ipodQty=case when itemName='ipod' then qty else 0 end
	from
		(select itemName, sum(qty) as qty from orderitem group by itemName) as vertical
) unmerged

The result of the execution is
productTotal

Solution #2: Using the Pivot function
Here I provide three example queries using the pivot function to generate (1)the total sold qty for each product; (2)the total sold quantity for each order; and (3)the total sold quantity for each product, grouped by orders.

(1) Total Sold Quantity for Each Product

select [ipad] as  ipadTotalQty, [iphone] as iphoneTotalQty, [ipod] as ipodTotalQty from
(select itemname, qty from orderitem ) as sourcetable
pivot
(
	sum(qty) for itemname in ([ipad] ,[iphone] ,[ipod])
) as pivotTable

This generates the same output as the table listed above.

(2) Total Sold Quantity for Each Order

--Using pivot function to get the total qty for each order
select  [100] as  order100Qty, [200] as order200Qty, [300] as order300Qty, [400] as order400Qty, [500] as order500Qty from
(select orderid, qty from orderitem ) as sourcetable
pivot
(
	sum(qty) for orderid in ([100],[200],[300],[400],[500])
) as pivotTable

The output is
orderTotal

(3) Total Sold Quantity for Each Product, Grouped by Orders

select orderid, [ipad] as  ipadTotalQty, [iphone] as iphoneTotalQty, [ipod] as ipodTotalQty from
(select orderid, itemname, qty from orderitem ) as sourcetable
pivot
(
	sum(qty) for itemname in ([ipad],[iphone],[ipod])
) as pivotTable

The output is
productOrderTotal

Advertisements
This entry was posted in Database and tagged , , . Bookmark the permalink.

One Response to T-SQL Pivots a Table

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