Wednesday 3 June 2020

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





No comments:

Post a Comment

If you have any doubt, please let me know.

Popular Posts