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:
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:
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:
[ALSO READ] PIVOT and UNPIVOT in Sql Server
Dynamic PIVOT in Sql Server
Wow, thank you so much for your tutorials and examples. This has been the best Dynamic Pivot exercise for me.
Thank you Antonio
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’.
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
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 ?
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.
Thanks Alex. Appreciate your comments.
Hi,
First, Thank you very much for the solution.
Second, how can i save the results of this query to the table?
Thanks you. You saved me a lot of time. God bless you in Jesus name amen.
I really appreciate you.
Hi,
First, Thank you very much for the solution.
Second, how can i implement same in Rdlc Reports?
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.
You are my hero for the day
Thank You for doing this
Many Articles online- Yours is nice to follow and easily understood.
Thank you, saves a bit of time for each report, which soon adds up.
thank you so much, it helped me to solve isnull issue…