UNPIVOT in SQL Server

UNPIVOT in SQL Server is just the opposite of PIVOT. I have written an article on PIVOT which you can refer here. There is another article that is written by me on Converting column header of table to row, the same functionality can be achieved by UNPIVOT. So, let's quickly jump into implementing it.

Create a temporary table

declare @t as table(ID int identity(1,1),Book varchar(20),PriceInJan int,PriceInFeb Int )

 

Populate the data

insert @t values('Fiction',20,31)

insert @t values('Science',15,17)

insert @t values('History',15,18)

insert @t values('Non-Fiction',12,17)

insert @t values('Autobiography',18,16)

insert @t values('Other',16,12)

--select * from @t

 

This will how UNPIVOT will work

select id,Book,ts.mm as Month,ts.t as Price

from @t

unpivot(t for mm in(PriceInJan,PriceInFeb))

as ts

 

Tags: , , , ,

SQL Server | SQL Tricks