PIVOT/UNPIVOT expression in SQL Server.

Pivot expression in SQL converts values returned by a query into columns. For example in below example, the temp table contains revenue data for different months for companies. If the result has to be displayed with each month as columns , then it can done easily using PIVOT expression.

---

Create temp table to store data

CREATE table #tmp (comp_id int,comp_name varchar(20), [month] int,rev int)

GO

insert into #tmp values (200,'comp1',1,32144)
insert into #tmp values (200,'comp1',2,56234)
insert into #tmp values (200,'comp1',3,1000)
insert into #tmp values (211,'comp2',1,2364)
insert into #tmp values (211,'comp2',2,8762)
insert into #tmp values (211,'comp2',3,7644)
insert into #tmp values (211,'comp2',3,1111)
insert into #tmp values (211,'comp2',3,1111)
insert into #tmp values (213,'comp3',3,1111)

Now if we want to show the month as columns (like ‘1’,’2’,’3’ etc) we can use PIVOT expression as given below

SELECT * FROM #tmp t PIVOT 
 (SUM(rev) FOR [month] IN ([1],[2],[3])) pvt

UNPIVOT

We can also UNPIVOT the result as given below

/*get  ‘pivot’ data into another temp table #tmp2*/

SELECT * INTO #tmp1 FROM #tmp t PIVOT 
 (SUM(rev) FOR [month] IN ([1],[2],[3])) pvt

/*UNPIVOT the data*/
SELECT * FROM #tmp1 t  UNPIVOT 
 (rev FOR [month] IN ([1],[2],[3])) pvt