DECLARE @sql AS NVARCHAR(max); DECLARE @columnNames AS NVARCHAR(max), @columnNamesNotNull nvarchar(max); SELECT @columnNames = stuff((select ', '+ QUOTENAME(saveinit) FROM (SELECT DISTINCT saveinit FROM #initials) AS x FOR XML PATH('')),1,2,''); SELECT @columnNamesNotNull = stuff((select ', COALESCE('+ QUOTENAME(saveinit) + ', 0) as ' + quotename(saveInit) FROM (SELECT DISTINCT saveinit FROM #initials) AS x FOR XML PATH('')),1,2,''); SET @sql = N'SELECT recvdate, ' + @columnNamesNotNull + 'FROM #initials PIVOT(SUM(numprocessed) FOR saveinit IN (' + @columnNames + ')) AS PivotSales'; EXEC sp_executesql @sql;>This is a learning excercise for me and the code is snipped from something larger.
>SELECT >cast(recvdate as date) as recvdate,saveinit,count(*) AS numProcessed >into #initials >FROM PoReceiptsReport >WHERE CAST(recvdate as date) > DATEADD(month,-4,getdate()) >GROUP BY CAST(recvdate as Date),saveinit >ORDER BY CAST(recvdate as Date),saveinit > > >DECLARE @sql AS NVARCHAR(2000); >DECLARE @saveinitvalues AS NVARCHAR(2000); > >SELECT @saveinitvalues = ISNULL(@saveinitvalues + ', ', '') + QUOTENAME(saveinit) >FROM (SELECT DISTINCT saveinit FROM #initials) AS recvdate; > >SET @sql = > N'SELECT recvdate, ' + @saveinitvalues + > 'FROM #initials > PIVOT(SUM(numprocessed) > FOR saveinit IN (' + @saveinitvalues + ')) AS PivotSales'; > >EXEC sp_executesql @sql; >>