The code below works great where I have months (Allocmonth) that vary each time it's created. My problem is that instead of the results being presented, I want the results in a View, which SQL apparently does not allow. Any suggestions?
DECLARE @columns VARCHAR(max)
DECLARE @TotalColumns VARCHAR(max)
SELECT @columns = COALESCE(@columns + ',
[' + cast(ALLOCMONTH as varchar) + ']',
'[' + cast(ALLOCMONTH as varchar)+ ']')
FROM PIVOTTESTVIEW
GROUP BY ALLOCMONTH
ORDER BY ALLOCMONTH
DECLARE @query VARCHAR(max)
SET @query = 'SELECT *
FROM PIVOTTESTVIEW
PIVOT (SUM(AMOUNT) FOR ALLOCMONTH IN
('+ @columns +')) AS TOTALAMOUNT'
EXECUTE(@query)