PIVOT and UNPIVOT code sample

Writing PIVOT and UNPIVOR statements is tricky, I will explain with simple example for easy understanding. You may try on your own in 2 minutes

• Create a table with Month, WeekDay, Subtotal

-- Creating a simple able for easy understanding 
USE AdventureWorks2012
IF (object_ID('tempdb..#SalesMonthDay') is not null)
	DROP Table #SalesMonthDay

select DATENAME(MM,OrderDate) as SalesMonth, DATENAME(DW,OrderDate) as SalesDay
, cast(SubTotal as decimal(10)) as SubTotalSales 
into #SalesMonthDay
from [Sales].[SalesOrderHeader]
where OrderDate between '2006-01-01' and '2007-01-01'

select * from #SalesMonthDay

PIVOT01

• PIVOT table data to view in Grid format

-- Pivot example 
select * from #SalesMonthDay
PIVOT 
( SUM(SubTotalSales)
 for SalesMonth in (April, August, December, February,January,July,June,March,May,November,October,September) 
 )AS pvtTable

PIVOT02

• Create a new Grid table for UNPIVOT demo

IF (object_ID('tempdb..#PIVOTedTable') is not null)
	DROP Table #PIVOTedTable
select * into #PIVOTedTable 
from #SalesMonthDay
PIVOT 
( SUM(TotalSales)
 for SalesMonth in (April, August, December, February,January,July,June,March,May,November,October,September) 
 )AS pvtTable

 select * from #PIVOTedTable 

UNPIVOT01

• UNPIVOT table

select  SalesDay, SalesMonth, TotalSales from #PIVOTedTable 
 UNPIVOT 
 (
 TotalSales FOR SalesMonth in (April, August, December, February,January,July,June,March,May,November,October,September)
 ) as unpvttbl

UNPIVOT02

Advertisements
This entry was posted in Script, SQL, SQL Query and tagged , , . Bookmark the permalink.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s