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.

**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

**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

(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

### Like this:

Like Loading...

*Related*

Reblogged this on SutoCom Solutions.