For better Understanding of Pivot,UnPivot,Rank Concepts go through this examples

Pivot

declare @Batting table


(Player varchar(10), Year int, Team varchar(10), HomeRuns int, primary key(Player,Year))

insert into @Batting

select 'A',2001,'Red Sox',13 union all

select 'A',2002,'Red Sox',23 union all

select 'A',2003,'Red Sox',19 union all

select 'A',2004,'Red Sox',14 union all

select 'A',2005,'Red Sox',11 union all

select 'B',2001,'Yankees',42 union all

select 'B',2002,'Yankees',39 union all

select 'B',2003,'Yankees',42 union all

select 'B',2004,'Yankees',29 union all

select 'C',2002,'Yankees',2 union all

select 'C',2003,'Yankees',3 union all

select 'C',2004,'Red Sox',6 union all

select 'C',2005,'Red Sox',9

select * from

(select Player,[Year],Team,HomeRuns from @Batting) as Bvt

pivot( count(HomeRuns) for team in ([Red Sox],[Yankees]))
as pvt
 
Rank
 
select Player, Year, HomeRuns, Rank() over (Partition BY [year] order by HomeRuns DESC) as Rank


from @Batting
 
UnPivot
 
Create TABLE pvt (VendorID int, Emp1 int, Emp2 int,


Emp3 int, Emp4 int, Emp5 int);

GO

INSERT INTO pvt VALUES (1,4,3,5,4,4);

INSERT INTO pvt VALUES (2,4,1,5,5,5);

INSERT INTO pvt VALUES (3,4,3,5,4,4);

INSERT INTO pvt VALUES (4,4,2,5,5,4);

INSERT INTO pvt VALUES (5,5,1,5,5,5);

GO

--Unpivot the table.

SELECT VendorID, Employee, Orders

FROM



(SELECT VendorID, Emp1, Emp2, Emp3, Emp4, Emp5

FROM pvt) p

UnPIVOT

( Orders FOR Employee IN

(Emp1, Emp2, Emp3, Emp4, Emp5)

) AS unpvt;



Drop pvt