— PIVOT AND UNPIVOT EXAMPLES
— Create Tables First

BEGIN
(
select Name, IsNull([Duck],0) as duckers, IsNull([Pig],0) as piggers, IsNull([Frog],0) as froggers, isNull([Rhino],0) as rhinos, IsNull([Monkey],0) as Monkers
–Into #TempBruce
From
(
select
Name, ProductName, (quan * p.cost) As Amount
from invoice as i
join product as p
on i.productid = p.ProductId
Join customer as c
on i.custid = c.custid
) as P

Pivot
(
Sum(Amount)
For ProductName In ([Duck],[Pig],[Frog],[Rhino],[Monkey])
) as pvt
)
END

–****** Unpivot *********
— Haven’t figured this out yet…
—
— Show what the Table is like
select * from #TempBruce
—

select Name, Amount, Product
–, Amount
From
(select Name, duckers, piggers, froggers, rhinos, monkers
from #TempBruce) as p2
Unpivot
( Amount for Product in (duckers, piggers, froggers, rhinos, monkers)) as up;
–amount for amounts In (duckers, piggers, froggers, rhinos, monkers)) as up;
—*************** UnPivot ************************