Tag Archives: REMOVE NULL from PIVOT Result

How to replace NULL value by 0 in the Dynamic Pivot result – Sql Server

Sql Server, , , , , , , , ,

For the previous articles PIVOT and UNPIVOT in Sql Server and Dynamic PIVOT in Sql Server, recieved couple of comments requesting: how to replace NULL value by 0 in the PIVOT result?. In this article let us understand replacing NULL values in the PIVOT result with an example.

PROBLEM

Below is the pictorial explanation of this problem of NULL value in the PIVOT result:

Replace Null by Zero PIVOT result Sql Server 1

ScriptBelow is the script to create the temporary table #CourseSales table with sample data as depicted in the above image:

CREATE TABLE #CourseSales
 (Course VARCHAR(50),Year INT,Earning MONEY)
 GO
 --Populate Sample records
 INSERT INTO #CourseSales VALUES('.NET',2012,10000)
 INSERT INTO #CourseSales VALUES('.NET',2012,5000)
 INSERT INTO #CourseSales VALUES('Java',2012,20000)
 INSERT INTO #CourseSales VALUES('Java',2013,30000)
 GO

Below is the Dynamic PIVOT script to generate the PIVOT result with NULL value as shown in the above image:

DECLARE @DynamicPivotQuery AS NVARCHAR(MAX)
 DECLARE @ColumnName AS NVARCHAR(MAX)
 --Get distinct values of the PIVOT Column
 SELECT @ColumnName= ISNULL(@ColumnName + ',','')
 + QUOTENAME(Course)
 FROM (SELECT DISTINCT Course FROM #CourseSales) AS Courses
 --Prepare the PIVOT query using the dynamic
 SET @DynamicPivotQuery =
 N'SELECT Year, ' + @ColumnName + '
 FROM #CourseSales
 PIVOT(SUM(Earning)
 FOR Course IN (' + @ColumnName + ')) AS PVTTable'
 --Execute the Dynamic Pivot Query
 EXEC sp_executesql @DynamicPivotQuery
 GO

RESULT of the above query:
Replace Null by Zero PIVOT result Sql Server 2

SOLUTION

We can modify the previous query like below to replace NULL by 0 in the dynamic PIVOT query result:

DECLARE @DynamicPivotQuery AS NVARCHAR(MAX),
        @PivotColumnNames AS NVARCHAR(MAX),
        @PivotSelectColumnNames AS NVARCHAR(MAX)
--Get distinct values of the PIVOT Column
SELECT @PivotColumnNames= ISNULL(@PivotColumnNames + ',','')
+ QUOTENAME(Course)
FROM (SELECT DISTINCT Course FROM #CourseSales) AS Courses
--Get distinct values of the PIVOT Column with isnull
SELECT @PivotSelectColumnNames 
	= ISNULL(@PivotSelectColumnNames + ',','')
	+ 'ISNULL(' + QUOTENAME(Course) + ', 0) AS ' 
	+ QUOTENAME(Course)
FROM (SELECT DISTINCT Course FROM #CourseSales) AS Courses
--Prepare the PIVOT query using the dynamic
SET @DynamicPivotQuery =
N'SELECT Year, ' + @PivotSelectColumnNames + '
FROM #CourseSales
PIVOT(SUM(Earning)
FOR Course IN (' + @PivotColumnNames + ')) AS PVTTable'
--Execute the Dynamic Pivot Query
EXEC sp_executesql @DynamicPivotQuery

RESULT:
Replace Null value by Zero in the Dynamic PIVOT result Sql Server 3

[ALSO READ] PIVOT and UNPIVOT in Sql Server
Dynamic PIVOT in Sql Server