Dynamic pivot with the sum columns wise in the sql server

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:  https://bageshkumarbagi-msbi.blogspot.com/2020/06/dynamic-pivot-in-sql-server.html

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.