Level Extreme platform
Subscription
Corporate profile
Products & Services
Support
Legal
Français
Nulls in Pivot Table
Message
 
 
To
07/10/2015 17:28:05
General information
Forum:
Microsoft SQL Server
Category:
SQL syntax
Environment versions
SQL Server:
SQL Server 2014
Application:
Web
Miscellaneous
Thread ID:
01625679
Message ID:
01625682
Views:
39
From the top of my head:
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.
>I found a really good article on how to create pivot tables and have modified the examples
>into code which produces something very close to the output I need
>
>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;
>
>
>My question for the moment...........
>
>The result set shows NULL if values for SAVEINIT are not found for each date.
>
>Is there a way to convert these NULLS into 0?
>
>Thanks...........Rich
>
>BTW.......Here is a link to the article in case anybody is interested
>https://www.simple-talk.com/sql/t-sql-programming/questions-about-pivoting-data-in-sql-server-you-were-too-shy-to-ask/
If it's not broken, fix it until it is.


My Blog
Previous
Next
Reply
Map
View

Click here to load this message in the networking platform