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.
In a previous post, we learn how to create a dynamic pivot:
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.
