Replacing null values to zero in dynamic pivot in sql server

In the previous post, we see how to get quarterly sales using dynamic pivot in the SQL server. In this result set we are getting some null value.

Database:  AdventureWorks2017

Table:  sales.SalesOrderHeader

DECLARE     @columns NVARCHAR(MAX) = ''; DECLARE     @sqlquery NVARCHAR(MAX) = ''; set          @columns= (SELECT distinct ',' + QUOTENAME(CAST('Q'+CAST(DATEPART(QUARTER, s.OrderDate) AS VARCHAR(1)) AS VARCHAR(2)))     FROM    sales.SalesOrderHeader s order by 1 FOR XML PATH('')) SET @columns = Right(@columns, LEN(@columns) - 1); set @sqlquery=' select sales_Year, '+@columns+' from ( Select  year(so.OrderDate) as sales_Year,   CAST(''Q''+CAST(DATEPART(QUARTER, so.OrderDate) AS VARCHAR(1)) AS VARCHAR(2)) Quarters, so.SubTotal as Total_Sales  FROM sales.SalesOrderHeader so  )  as sales  pivot  (   SUM(Total_Sales)   FOR Quarters IN ('+@columns+')  )pvt  order by sales_year  ';   -- execute the dynamic SQL EXECUTE sp_executesql @sqlquery;

Result set

 

In this Demo we will see how to replace the null value to the 0.

We need to create a dynamic columns which check the null value and when we will run this script it will replace the null value to zero.

DECLARE     @columns NVARCHAR(MAX) = ''; DECLARE     @sqlquery NVARCHAR(MAX) = ''; DECLARE                              @Columns_Rep_Null Nvarchar(Max)=''; set          @columns= (SELECT distinct ',' + QUOTENAME(CAST('Q'+CAST(DATEPART(QUARTER, s.OrderDate) AS VARCHAR(1)) AS VARCHAR(2)))     FROM    sales.SalesOrderHeader s order by 1 FOR XML PATH('')) SET @columns = Right(@columns, LEN(@columns) - 1); print @columns Set @Columns_Rep_Null=replace(replace(@columns,'[','isnull(['),']','],0)') print @Columns_Rep_Null

We will get the below column

isnull([Q1],0),isnull([Q2],0),isnull([Q3],0),isnull([Q4],0)

Now we are creating the dynamic query

DECLARE     @columns NVARCHAR(MAX) = ''; DECLARE     @sqlquery NVARCHAR(MAX) = ''; DECLARE                              @Columns_Rep_Null Nvarchar(Max)=''; set          @columns= (SELECT distinct ',' + QUOTENAME(CAST('Q'+CAST(DATEPART(QUARTER, s.OrderDate) AS VARCHAR(1)) AS VARCHAR(2)))     FROM    sales.SalesOrderHeader s order by 1 FOR XML PATH('')) SET @columns = Right(@columns, LEN(@columns) - 1); Set @Columns_Rep_Null=replace(replace(@columns,'[','isnull(['),']','],0)') set @sqlquery=' select sales_Year, '+@Columns_Rep_Null+' from ( Select  year(so.OrderDate) as sales_Year,   CAST(''Q''+CAST(DATEPART(QUARTER, so.OrderDate) AS VARCHAR(1)) AS VARCHAR(2)) Quarters, so.SubTotal as Total_Sales  FROM sales.SalesOrderHeader so  )  as sales  pivot  (   SUM(Total_Sales)   FOR Quarters IN ('+@columns+')  )pvt  order by sales_year  ';   -- execute the dynamic SQL EXECUTE sp_executesql @sqlquery;

Running this script.

   

With the help of the above script we replace the null value with zero. But the problem is that, we are not able to see the header of the column.

To overcoming with this issue in the dynamic script we need to generate the column and the union all with this result set.

See the below script. In this script we are going to generate the header row.

DECLARE     @columns NVARCHAR(MAX) = ''; DECLARE     @sqlquery NVARCHAR(MAX) = ''; DECLARE                              @Columns_Rep_Null Nvarchar(Max)=''; Declare                 @Header_columns Nvarchar(Max)=''; set          @columns= (SELECT distinct ',' + QUOTENAME(CAST('Q'+CAST(DATEPART(QUARTER, s.OrderDate) AS VARCHAR(1)) AS VARCHAR(2)))     FROM    sales.SalesOrderHeader s order by 1 FOR XML PATH('')) SET @columns = Right(@columns, LEN(@columns) - 1); Set @Columns_Rep_Null=replace(replace(@columns,'[','isnull(['),']','],0)') set @Header_columns=replace(@columns,'[','null as [') set @sqlquery=' select year(OrderDate) as sales_Year,'+ @Header_columns +' FROM sales.SalesOrderHeader where 1=2 union all select sales_Year, '+@Columns_Rep_Null+' from ( Select  year(so.OrderDate) as sales_Year,   CAST(''Q''+CAST(DATEPART(QUARTER, so.OrderDate) AS VARCHAR(1)) AS VARCHAR(2)) Quarters, so.SubTotal as Total_Sales  FROM sales.SalesOrderHeader so  )  as sales  pivot  (   SUM(Total_Sales)   FOR Quarters IN ('+@columns+')  )pvt  order by sales_year  ';   -- execute the dynamic SQL EXECUTE sp_executesql @sqlquery;

   

 

For the header row basically we are creating a dummy row

DECLARE     @columns NVARCHAR(MAX) = ''; DECLARE     @sqlquery NVARCHAR(MAX) = ''; DECLARE                              @Columns_Rep_Null Nvarchar(Max)=''; Declare                 @Header_columns Nvarchar(Max)=''; set          @columns= (SELECT distinct ',' + QUOTENAME(CAST('Q'+CAST(DATEPART(QUARTER, s.OrderDate) AS VARCHAR(1)) AS VARCHAR(2)))     FROM    sales.SalesOrderHeader s order by 1 FOR XML PATH('')) SET @columns = Right(@columns, LEN(@columns) - 1); Set @Columns_Rep_Null=replace(replace(@columns,'[','isnull(['),']','],0)') set @Header_columns=replace(@columns,'[','null as [') print @Header_columns

null as [Q1],null as [Q2],null as [Q3],null as [Q4]

Here we are creating the dummy header

select year(OrderDate) as sales_Year,'+ @Header_columns +' FROM sales.SalesOrderHeader where 1=2