In this demo, we will see how the get the sum of the all
columns in the dynamic pivot.
Below is my requirement.
Business wants the total sales year wise.
In this demo, we will see how we will get this result.
In the previous post, we learn how to create a dynamic pivot:
First of all, we need to create a dynamic column for the
sum.
SET @Row_sum_col =Replace(Replace(@columns,'[','sum(['),']','])')
|
It will return
sum([2011]),sum([2012]),sum([2013]),sum([2014])
|
In the dynamic query, we need to create a temp table the
store the result set and finally, we need to use the union all to add the sum of each column.
See the below 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(@columns,'[','sum(['),']','])')
set @sqlquery='
IF OBJECT_ID(''tempdb..#temp'') IS NOT NULL DROP TABLE #temp
select * into #temp from (
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
)x
select Name,CountryRegionCode,[Group],'+@columns+' from #temp
union all
select ''Total Sales''Name,null as CountryRegionCode,null as
[Group],'+@Row_sum_col+' from #temp ;';
-- execute the dynamic SQL
EXECUTE sp_executesql @sqlquery;
|
See the results
Get the expected result.
