How to Replace Nulls with Zero in Pivoted Data in SQL

How to replace Null values to 0 in dynamic Pivot columns in SQL Server

In SQL Server, T-SQL Queries, we may come across with a Scenario, where we need to replace the NULL values with 0, in the dynamic Pivot Columns.

We can handle that Scenario as explained in detail in the following Stored Procedure. In this Procedure we are doing the following steps: 

--Storing the Aggregated data from Source table into a Temp Table

--Pivoting the Data from the Temp table and Inserting into another Temp Table

--Replacing the NULL values with 0 in Pivoted Data.

--Finally UnPivoting the updated Pivoted data and inserting into a Temp Table.

Please note that, you can consider only the steps that are relevant to your Scenario.

USE AnalyticsDB

GO

CREATE PROCEDURE [dbo].[SP_Audit_Procecss_Summary]

AS

DECLARE @vQuery NVARCHAR(4000),@vSQL NVARCHAR(4000), @vColumns NVARCHAR(4000), @vRowCount INT, @Val1 INT, @vDate VARCHAR(255);

BEGIN

--Storing the Source Data in a Temp Table

IF OBJECT_ID('[tempdb].[dbo].[##Temp_Process_Audit]', 'U') IS NOT NULL

BEGIN

   Drop Table [dbo].[##Temp_Process_Audit] ;

   Print ('Temp Table [##Temp_Process_Audit] has Dropped and Re-created')

END ;

SELECT * INTO [dbo].[##Temp_Process_Audit]

FROM ( Select [Audit_Date], [Quality_Rule], [Region], [Procecss], 

              SUM ([QR_Fail]) AS Total_QR_Fail

      From [dbo].[Process Audit Details]

      Group By [Audit_Date], [Quality_Rule], [Region], [Procecss]

   ) S1 ORDER BY S1.Audit_Date ;

--SELECT * FROM [dbo].[##Temp_Process_Audit]

/*

--Storing the Distinct [Audit_Date] values in a Temp Table which will used as reference, 

  while we replacing the NULL values in the Pivoted Data.

*/

IF OBJECT_ID('[tempdb].[dbo].[##Temp_Dist_AuditDates]', 'U') IS NOT NULL

BEGIN

   Drop Table [dbo].[##Temp_Dist_AuditDates] ;

   Print ('Temp Table [##Temp_Dist_AuditDates] has Dropped and Re-created')

END ;

SELECT DISTINCT  [Audit_Date], IDENTITY (INT) AS INT_Id 

INTO [dbo].[##Temp_Dist_AuditDates] FROM [dbo].[##Temp_Process_Audit] ;

--SELECT * FROM [dbo].[##Temp_Dist_AuditDates];

-Pivoting the Data using XML Method and Storing into a Table

IF OBJECT_ID('[tempdb].[dbo].[##Temp_Process_Audit_Pivot]', 'U') IS NOT NULL

BEGIN

   Drop Table [dbo].[##Temp_Process_Audit_Pivot] ;

   Print ('Temp Table [##Temp_Process_Audit_Pivot] has Dropped and Re-created')

END ;

--Getting the list of Unique [Audit_Date] values which will used as new columns in Pivot

SET @vColumns=STUFF (

    ( SELECT DISTINCT ',' + QUOTENAME(S1.[Audit_Date])

       FROM [dbo].[##Temp_Process_Audit] S1

       FOR XML PATH (''), TYPE).value('.' , 'NVARCHAR(4000)' ),

        1, 1, '');

SET @vQuery=' SELECT [Quality_Rule], [Region], [Procecss], '+@vColumns+'

  INTO [dbo].[##Temp_Process_Audit_Pivot] 

  FROM ( SELECT [Audit_Date], [Quality_Rule], [Region], [Procecss],                                         [Total_QR_Fail] FROM [dbo].[##Temp_Process_Audit] )

                          PIVOTSOURCE  PIVOT ( MAX([Total_QR_Fail]) FOR 

                          [Audit_Date] IN (' +@vColumns+ ') ) Pvt ' ;

EXECUTE (@vQuery);

--SELECT * FROM [dbo].[##Temp_Process_Audit_Pivot] ;

--Replacing NULL values with 0 in the Pivoted Data

SELECT @vRowCount=COUNT(*) FROM [dbo].[##Temp_Dist_AuditDates];

SET @Val1=1;

WHILE @Val1<=@vRowCount

BEGIN

SELECT @vDate=[Audit_Date] FROM [dbo].[##Temp_Dist_AuditDates]

WHERE INT_Id=@Val1

SET @vSQL='UPDATE [dbo].[##Temp_Process_Audit_Pivot]  

                SET [' + @vDate + ']=0 WHERE [' + @vDate + '] IS NULL' ;

EXEC (@vSQL)

SET @Val1=@Val1+1

END;

--SELECT * FROM [dbo].[##Temp_Process_Audit_Pivot] ;

--Finally UnPivoting the updated Pivoted Data

IF OBJECT_ID('[tempdb].[dbo].[##Temp_Process_Audit_UnPivot]', 'U') IS NOT NULL

BEGIN

    Drop Table [dbo].[##Temp_Process_Audit_UnPivot] ;

    Print ('Temp Table [##Temp_Process_Audit_UnPivot] has Dropped and Re-created')

END ;

SET @vQuery='SELECT [Audit_Date], [Quality_Rule], [Region], [Procecss],[Total_QR_Fail]
                          INTO [dbo].[##Temp_Process_Audit_UnPivot]  

  FROM [dbo].[##Temp_Process_Audit_Pivot]

UNPIVOT ( [Total_QR_Fail]  FOR [Audit_Date] IN (' +@vColumns+ ') ) UnPvt ' ;

EXECUTE (@vQuery);

END

--SELECT * FROM [dbo].[##Temp_Process_Audit_UnPivot] ;

Notes:

If the Pivoted table has 16 rows with 3 Pivoted Columns, when we UnPivot it, we will get 16* 3 =48 rows.

--------------------------------------------------------------------------------------------------------

Thanks, TAMATAM ; Business Intelligence & Analytics Professional

--------------------------------------------------------------------------------------------------------