There are instances when we want to have data displayed in Pivot table format for interactive data analysis and helping us in better decision making. This is easier to build in MS-Access or any BI tool. But this can be a little time consuming if this has to be performed by just using SQL queries. Here are few examples on different scenarios.
Say we have a table that contains Breverage details
Create table Breverage
(ID int Identity(1,1) NOT NULL,
Note**: In MS SQL Server 2008 a new “DATE” data type is introduced, so we no longer need to worry about separating date from time.
In above DatePurchased as date would not work in version below server 2008 and henceforth Datetime will have to be used in those conditions.
Let’s populate it with some data
Insert Breverage values('Lemonade', 20, '20100101')
Insert Breverage values('Soda', 10, '20100201')
Insert Breverage values('Wine', 25, '20100301')
Insert Breverage values('Chai', 20, '20100401')
Insert Breverage values('Espresso', 15, '20100501')
Insert Breverage values('Milkshake', 8, '20100601')
Insert Breverage values('Pepsi', 12, '20100601')
Insert Breverage values('Sweet Tea', 6, '20100701')
Insert Breverage values('Margarita', 18, '20100701')
Insert Breverage values('Coke', 21, '20100901')
Insert Breverage values('Latte', 17, '20100901')
Insert Breverage values('Water', 5, '20101001')
Insert Breverage values('Syrups', 9, '20101101')
Insert Breverage values('Juice', 30, '20101201')
Below will be our table after inserting data
,ISNULL([January],0) as January,ISNULL([February],0) as February
,ISNULL([March],0) March,ISNULL([April],0) April,ISNULL([May],0) May
,ISNULL([June],0) June,ISNULL([July],0) July,ISNULL([August],0) August
,ISNULL([September],0) September,ISNULL([October],0) October ,ISNULL([November],0) November
BreverageName, CASE (datepart(Month, DatePurchased))
WHEN 1 THEN 'January'
WHEN 2 THEN 'February'
WHEN 3 THEN 'March'
WHEN 4 THEN 'April'
WHEN 5 THEN 'May'
WHEN 6 THEN 'June'
WHEN 7 THEN 'July'
WHEN 8 THEN 'August'
WHEN 9 THEN 'September'
WHEN 10 THEN 'October'
WHEN 11 THEN 'November'
WHEN 12 THEN 'December'
END as [Month],
FROM Breverage where datepart(year, DatePurchased)='2010'
FOR [Month] IN