Wednesday 3 June 2020

Dynamic pivot with the sum of all columns rows wise in the sql server


In this demo, we will see how the get the sum of the all columns in rows wise in the dynamic pivot.
Below is my requirement.
   



We are using that script we will get the below result.
DECLARE     @columns NVARCHAR(MAX) = '';
DECLARE     @sqlquery NVARCHAR(MAX) = '';
set          @columns=
(SELECT distinct ',' +
     QUOTENAME(year(orderdate) )
FROM    sales.SalesOrderHeader s
order by 1
FOR XML PATH(''))

SET @columns = Right(@columns, LEN(@columns) - 1);
set @sqlquery='
select
Name,
CountryRegionCode,[Group],
'+@columns+' from
(
Select
 year(so.OrderDate) as sales_Year,st.Name,st.CountryRegionCode,st.[Group]
,so.SubTotal as Total_Sales
 FROM sales.SalesOrderHeader so
 join Sales.SalesTerritory st on st.TerritoryID=so.TerritoryID
 )  as sales
 pivot
 (
  SUM(Total_Sales)
  FOR Sales_Year IN ('+@columns+')
 )pvt
 order by name;';
  -- execute the dynamic SQL
EXECUTE sp_executesql @sqlquery;


 


In the dynamic pivot, we need to get add the code to get the sum of these columns.
Let’s see
We need to prepare the dynamic expression of the sum like below.
isnull([2011],0)+isnull([2012],0)+isnull([2013],0)+isnull([2014],0)

We the help of below script we will get the above expression.
SET @Row_sum_col =Replace(Replace(replace(@columns,',',''),'[','isnull(['),']','],0)+')
SET @Row_sum_col=left(@Row_sum_col,LEN(@Row_sum_col)-1)
Now we need to add this in the dynamic query.
DECLARE     @columns NVARCHAR(MAX) = '';
DECLARE     @sqlquery NVARCHAR(MAX) = '';
DECLARE     @Row_sum_col Nvarchar(MAx)='';
set       @columns=
(SELECT distinct ',' +
     QUOTENAME(year(orderdate) )
FROM    sales.SalesOrderHeader s
order by 1
FOR XML PATH(''))

SET @columns = Right(@columns, LEN(@columns) - 1);

SET @Row_sum_col =Replace(Replace(replace(@columns,',',''),'[','isnull(['),']','],0)+')
SET @Row_sum_col=left(@Row_sum_col,LEN(@Row_sum_col)-1)
set @sqlquery='
select
Name,
CountryRegionCode,[Group],
'+@columns+' ,'+@Row_sum_col +' as Total_Sales  from
(
Select
 year(so.OrderDate) as sales_Year,st.Name,st.CountryRegionCode,st.[Group]
,so.SubTotal as Total_Sales
 FROM sales.SalesOrderHeader so
 join Sales.SalesTerritory st on st.TerritoryID=so.TerritoryID
 )  as sales
 pivot
 (
  SUM(Total_Sales)
  FOR Sales_Year IN ('+@columns+')
 )pvt
 order by name;';

 print @sqlquery;

It will generate the below script
select
Name,
CountryRegionCode,[Group],
[2011],[2012],[2013],[2014] ,isnull([2011],0)+isnull([2012],0)+isnull([2013],0)+isnull([2014],0) as Total_Sales from
(
Select
 year(so.OrderDate) as sales_Year,st.Name,st.CountryRegionCode,st.[Group]
,so.SubTotal as Total_Sales
 FROM sales.SalesOrderHeader so
 join Sales.SalesTerritory st on st.TerritoryID=so.TerritoryID
 )  as sales
 pivot
 (
  SUM(Total_Sales)
  FOR Sales_Year IN ([2011],[2012],[2013],[2014])
 )pvt
 order by name;
Now we are running this dynamic script.
DECLARE     @columns NVARCHAR(MAX) = '';
DECLARE     @sqlquery NVARCHAR(MAX) = '';
DECLARE     @Row_sum_col Nvarchar(MAx)='';
set          @columns=
(SELECT distinct ',' +
     QUOTENAME(year(orderdate) )
FROM    sales.SalesOrderHeader s
order by 1
FOR XML PATH(''))

SET @columns = Right(@columns, LEN(@columns) - 1);

SET @Row_sum_col =Replace(Replace(replace(@columns,',',''),'[','isnull(['),']','],0)+')
SET @Row_sum_col=left(@Row_sum_col,LEN(@Row_sum_col)-1)
set @sqlquery='
select
Name,
CountryRegionCode,[Group],
'+@columns+' ,'+@Row_sum_col +' as Total_Sales from
(
Select
 year(so.OrderDate) as sales_Year,st.Name,st.CountryRegionCode,st.[Group]
,so.SubTotal as Total_Sales
 FROM sales.SalesOrderHeader so
 join Sales.SalesTerritory st on st.TerritoryID=so.TerritoryID
 )  as sales
 pivot
 (
  SUM(Total_Sales)
  FOR Sales_Year IN ('+@columns+')
 )pvt
 order by name;';
  -- execute the dynamic SQL
EXECUTE sp_executesql @sqlquery;

See the result.





No comments:

Post a Comment

If you have any doubt, please let me know.

Popular Posts