SQL PIVOT and UNPIVOT samples


create
table #tmp ( empid int, salesyear int , amt int);
insert
into #tmp
select
101, 2006, 100 union all
select
101, 2006, 200 union all
select
101, 2007, 300 union all
select
102, 2006, 400 union all
select
102, 2007, 500 union all
select
103, 2006, 600 union all
select
103 , 2007, 700
/* Pivot */

select
empid, [2006], [2007]
from
#tmp pivot (sum(amt) for salesyear in ([2006], [2007])) pvt
 

/* UnPivot */

select
empid,amt, salesyear
from

(select empid, [2006], [2007]
from #tmp pivot (sum(amt) for salesyear in ([2006], [2007])) pvt)v1
unpivot (amt for salesyear in ([2006],[2007])) upvt

-Shinu
Ebook Download
View all
Learn
View all