Ernio Ernio - 15 days ago 5
SQL Question

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".

Answer

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
Comments