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
|
