>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;>
>>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; >>>>