How to replace NULL value by 0 in the Dynamic Pivot result – 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

13 thoughts on “How to replace NULL value by 0 in the Dynamic Pivot result – Sql Server

  1. Wow, thank you so much for your tutorials and examples. This has been the best Dynamic Pivot exercise for me.

  2. DECLARE @ColumnName NVARCHAR(MAX), @DynamicPivotQuery NVARCHAR(MAX);

    –Get distinct values of the PIVOT Column
    SELECT @ColumnName= COALESCE(@ColumnName + ‘,’,”)
    + QUOTENAME(floor_name)
    FROM (select DISTINCT b.floor_name from tbl_roomno as a inner join tbl_floortype as b on a.floor_id=b.floor_id) AS Courses

    –Prepare the PIVOT query using the dynamic
    SET @DynamicPivotQuery =
    N’SELECT a.status, ‘ + @ColumnName + ‘
    tbl_roomno as a inner join tbl_floortype as b on a.floor_id=b.floor_id
    PIVOT(SUM(a.room_no)
    FOR b.floor_name IN (‘ + @ColumnName + ‘)) AS PVTTable’
    –Execute the Dynamic Pivot Query
    EXEC sp_executesql @DynamicPivotQuery

    I AM GETTING ERROR LIKE THIS
    Msg 156, Level 15, State 1, Line 2
    Incorrect syntax near the keyword ‘as’.

    1. select DISTINCT b.floor_name from tbl_roomno as a inner join

      select DISTINCT b.floor_name from tbl_roomno a inner join tbl_floortype b on a.floor_id=b.floor_id

  3. simply awesome …you solved so many problems for me. Thank you very much.
    One simple question for you. How do I write this into a table ?

  4. Unbelievable. I searched for days for a solution, and gave up. Thought I should search for it once more, and your post came up. I couldn’t believe my eyes. Thank you so much. It’s simple and elegant. Saved me from modifying the front end.

  5. Hi,
    First, Thank you very much for the solution.
    Second, how can i save the results of this query to the table?

  6. Sir this really awesome… Helped me a lot…
    But please help me get rid of one more problem with same pivot query….

    I want the pivot column in sorted way…Either by accending or descending order.
    Please help.

Leave a Reply

Your email address will not be published. Required fields are marked *