Plateforme Level Extreme
Abonnement
Profil corporatif
Produits & Services
Support
Légal
English
Nulls in Pivot Table
Message
De
07/10/2015 17:28:05
 
 
À
Tous
Information générale
Forum:
Microsoft SQL Server
Catégorie:
Syntaxe SQL
Titre:
Nulls in Pivot Table
Versions des environnements
SQL Server:
SQL Server 2014
Application:
Web
Divers
Thread ID:
01625679
Message ID:
01625679
Vues:
46
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/
Suivant
Répondre
Fil
Voir

Click here to load this message in the networking platform