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,

BreverageName varchar(60),

ListPrice money,

DatePurchased date)

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

Query

select BreverageName

,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

,ISNULL([December],0) December

from(

SELECT *

FROM (

  SELECT

    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],

    ListPrice

  FROM Breverage  where datepart(year, DatePurchased)='2010'

) TableDate

PIVOT

(

  sum(ListPrice)

  FOR [Month] IN

   (

[January],[February],[March],[April],[May],[June],[July],[August],[September],[October],[November],[December]

    )

  ) TblPivot