CODEWITHSUNDEEP
sqlsql-serverpivotpivot-table
Ernio
Ernio

Reputation: 978

Perform pivot without PIVOT operator

I am having difficulty designing procedure that would perform pivot with given aggregate on table given later in question. Procedure should dynamically take all entries from Sales.Month and pivot table on it using aggregate passed into procedure, so for example if we would pass SUM (passing using VARCHAR and then EXEC on dynamically created query) on Sales:

Sales:

Item   Month  Price
-------------------
Book     Jan    230
Book     Jan    100
Game     Jan     50
Game     Feb     80
Stick    Mar    190

Totals: ("pivoted")

Item     Jan   Feb   Mar
------------------------
Book     330  null  null
Game      50    80  null
Stick   null  null   190

I can't really come up with syntax that would allow me to do that.

Edit note: Main difficulty here is actually "Not using 'native' PIVOT".

Upvotes: 2

Views: 1552

Answers (1)

John Cappelletti
John Cappelletti

Reputation: 82000

You may notice I have a sub-query to keep the month columns in proper order. Also, I tend to prefer conditional aggregation because it is easier to add additional columns (i.e. Grand Total)

Declare @Agg varchar(25) = 'SUM'  -- Try Min, Max, Avg, ...

Declare @SQL varchar(max)=''
Select @SQL = @SQL+','+MMM+'='+@Agg+'(case when Month='''+MMM+''' then Price else null end)'
 From (Select MM,MMM From (Values(1,'Jan'),(2,'Feb'),(3,'Mar'),(4,'Apr'),(5,'May'),(6,'Jun'),(7,'Jul'),(8,'Aug'),(9,'Sep'),(10,'Oct'),(11,'Nov'),(12,'Dec')) M(MM,MMM)) A
 Where MMM in (Select Distinct Month from Yourtable)
 Order By MM

Select @SQL='Select Item'+@SQL+' From Yourtable Group By Item'
Exec(@SQL)

Returns

Item    Jan     Feb     Mar
Book    330.00  NULL    NULL
Game    50.00   80.00   NULL
Stick   NULL    NULL    190.00

FYI: The dynamic SQL Generated:

Select Item
      ,Jan=SUM(case when Month='Jan' then Price else null end)
      ,Feb=SUM(case when Month='Feb' then Price else null end)
      ,Mar=SUM(case when Month='Mar' then Price else null end) 
 From Yourtable 
 Group By Item

Upvotes: 4

Related Questions