Dynamically pivot distinct values into column headers (ie months)

Question

I'm using SQL Server 2008 R2 and was hoping for some help to dynamically create a column for each distinct date (month) I have in my dataset, where I can SUM the quantity per month by user. So for example my data looks like:

Emp     Month       QTY
-------------------------
John    3/1/2016    20
John    3/1/2016    30
John    4/1/2016    15
John    6/1/2016    40
Jim     2/1/2016    25
Jim     4/1/2016    28
Jim     5/1/2016    15
Jim     5/1/2016    15
Jim     6/1/2016    32
Alex    4/1/2016    20
Alex    4/1/2016    20
Alex    4/1/2016    20
Alex    5/1/2016    45

and I'd like to just get the totals per month by Emp, like this:

Employee    2/1/2016    3/1/2016    4/1/2016    5/1/2016    6/1/2016
--------------------------------------------------------------------
John        0           50          15          0           40
Jim         25          0           28          30          32
Alex        0           0           60          45          0

I've seen some PIVOT and CROSS APPLY functions where the columns (month in this case) are specified and hardcoded before hand. Is there an easy way to dynamically create those column names based on the values within [Month] ?

Either way, any help is greatly appreciated.

Take a look here: http://stackoverflow.com/questions/10404348/sql-server-dynamic-pivot-query — vercelli, Jul 05 '16 at 20:00

John Cappelletti · Answer 1 · 2016-07-05T20:43:33.060

PIVOT is great, but I use a stored procedure for the bulk of my Dynamic Pivots.

The source can be a table,view,#temp, or even sql

Notice that you can define additional aggregates (ie. Total, Min, Max, Avg, STD...). You can also define additional Group By's as well

Exec [prc-Pivot] '#Temp','Month','sum(Qty)[]','Emp','count(*)[Records],sum(Qty)[Total],min(Qty)[Min],max(Qty)[Max],Avg(Qty)[Avg]'

Returns

And, just for fun...

Exec [prc-Pivot] 'Select *,Qtr=''Q''+concat(concat(DatePart(QQ,Month),''-''),Year(Month)) from #Temp','Emp','sum(Qty)[]','Qtr',null

The Stored Procedure

CREATE PROCEDURE [dbo].[prc-Pivot] (
    @Source varchar(1000),          -- Any Table or Select Statement
    @PvotCol varchar(250),          -- Field name or expression ie. Month(Date)
    @Summaries varchar(250),        -- aggfunction(aggValue)[optionalTitle]
    @GroupBy varchar(250),          -- Optional additional Group By 
    @OtherCols varchar(500) )       -- Optional Group By or aggregates
AS

--Exec [prc-Pivot] 'Select Year=Year(TR_Date),* From [Chinrus-Series].[dbo].[DS_Treasury_Rates]','''Q''+DateName(QQ,TR_Date)','avg(TR_Y10)[-Avg]','Year','count(*)[Records],min(TR_Y10)[Min],max(TR_Y10)[Max],Avg(TR_Y10)[Avg]'


Set NoCount On
Set Ansi_Warnings Off

Declare @Vals varchar(max),@SQL varchar(max);
Set @Vals = ''
Set @OtherCols= IsNull(', ' + @OtherCols,'')
Set @Source = case when @Source Like 'Select%' then @Source else 'Select * From '+@Source end
Create Table #TempPvot  (Pvot varchar(100))
Insert Into #TempPvot
Exec ('Select Distinct Convert(varchar(100),' + @PvotCol + ') as Pvot FROM (' + @Source + ') A')
Select @Vals = @Vals + ', isnull(' + Replace(Replace(@Summaries,'(','(CASE WHEN ' + @PvotCol + '=''' + Pvot +  ''' THEN '),')[', ' END),0) As [' + Pvot ) From #TempPvot Order by Pvot
Drop Table #TempPvot
Set @SQL = Replace('Select ' + Isnull(@GroupBy,'') + @OtherCols + @Vals + ' From (' + @Source + ') PvtFinal ' + case when Isnull(@GroupBy,'')<>'' then 'Group By ' + @GroupBy + ' Order by ' + @GroupBy else '' end,'Select , ','Select ')
--Print @SQL
Exec (@SQL)


Set NoCount Off
Set Ansi_Warnings on

Dynamically pivot distinct values into column headers (ie months)

1 Answers1