How to pivot data by month in SQL and output to a temporary table

There are some things that aren't easy to do in SQL as you'd like. I don't know why it has to make the seemingly simple things to complete in Excel so annoyingly tricky.

Dynamic pivots is one of those things that is overcomplicated, sure there's plenty of tutorials on the t'internet , but what they all seem to lack is instructions of how to write them to a temporary table, I'm a big fan of temp tables in my workflows and on this occasion google has been drawing blanks.

So if you are struggling to work it out you have to use a global temporary table as EXEC command you run actually uses a different process so your process won't see it after it has run. I also had a couple of attemps to get it in the right place - SQL isn't really helpful in this respect either - the code highlighting doesn't extend that far.

Anyway, I'll get to the example so you can see for yourself:

Print 'Pivot transactions'

begin try drop table ##mcTemp1 end try begin catch end catch

DECLARE @ColumnName AS NVARCHAR(MAX)
DECLARE @Qry AS NVARCHAR(MAX)
DECLARE @FromDate AS NVARCHAR(MAX)
DECLARE @ToDate AS NVARCHAR(MAX)

Set @ToDate='2015-10-31'
Set @FromDate=DateAdd(MONTH,-24,@ToDate)+1

SELECT @ColumnName= ISNULL(@ColumnName + ',','') 
       + QUOTENAME(Month)
FROM (SELECT DISTINCT Convert(Date,[PAY_DATE]-DAY([PAY_DATE])+1) AS 'Month' FROM [WHS_PAYMENT_HISTORY] 
WHERE PAY_DATE between @FromDate and @ToDate) AS Months
ORDER BY MONTH


Set @Qry = N'SELECT ACCOUNT_NUMBER,'+@ColumnName +'
INTO ##mcTemp1
from
(
SELECT ACCOUNT_NUMBER,
	[PAY_DATE]-DAY([PAY_DATE])+1 AS ''Month''
    ,[PAY_VAL] As Payment
  FROM [WHS_PAYMENT_HISTORY]
  WITH(NOLOCK)
  WHERE PAY_DATE between ''' + @FromDate + ''' and ''' + @ToDate + '''
) pd
pivot
(
SUM([Payment])
FOR [Month] in (' + @ColumnName+')
) As Pvt'

EXEC sp_executesql @Qry

SELECT * FROM ##mcTemp1