Saturday, December 5, 2020

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

No comments:

Post a Comment

Hi User, Thank You for visiting My Blog. Please post your genuine Feedback or comments only related to this Blog Posts. Please do not post any Spam comments or Advertising kind of comments which will be Ignored.