December 17, 2016

SQL pivot


/* Dynamically pivot rows into columns  */

DECLARE @cols varchar(max)
,@query varchar(max)

SELECT @cols = COALESCE(@cols + ',[' , '') +convert(varchar(30),[loan date],101) + ']' FROM #EOMdates ORDER BY [SortID]
SET @cols = '[' + @cols 

SET @query = 'SELECT [status], ' + @cols + ' INTO ##pivoted FROM 
(
SELECT [Loan Date]
, [count] = isnull(loans,0)
, [status]
FROM #buckets
) data
PIVOT
(
Sum([count])
FOR [Loan Date] IN (' + @cols + ' )
) AS PivotTable'
EXECUTE(@query)