Level Extreme platform
Subscription
Corporate profile
Products & Services
Support
Legal
Français
Nulls in Pivot Table
Message
From
07/10/2015 17:28:05
 
 
To
All
General information
Forum:
Microsoft SQL Server
Category:
SQL syntax
Title:
Nulls in Pivot Table
Environment versions
SQL Server:
SQL Server 2014
Application:
Web
Miscellaneous
Thread ID:
01625679
Message ID:
01625679
Views:
47
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/
Next
Reply
Map
View

Click here to load this message in the networking platform