SUM of dynamic Columns in PIVOT table

Question

HI

i need to add grand total in pivot column .

DECLARE @Status int=1
Declare @STARTDATEkey   int
Declare @ENDDATEkey    int

set @STARTDATEkey=20230525
set @ENDDATEkey=20230529


If exists (select * from tempdb.sys.all_objects where name like '#TMP_DATEatt%' )
BEGIN    
      DROP TABLE #TMP_DATEatt
END 

 DECLARE @Query1 NVARCHAR(MAX)
DECLARE @pivv NVARCHAR(MAX),@Query NVARCHAR(MAX),   @column NVARCHAR(MAX),@Columns2 NVARCHAR(MAX)
 
;WITH Sales_CTE (RETAILERID, CompanyName, L1,L2,L3,L4,L5,Posteddatekey,Toupamount,Deno)  
AS  
 (  
    select b.RETAILERID,B.CompanyName,
sm.emp_Name L1,  
    ss.emp_Name L2,  
    sP.emp_Name L3,  
    sN.emp_Name L4,  
    sk.emp_Name L5,
	Posteddatekey,
	sum(Amount) Toupamount,
	sum(Deno_2000_count) Deno
from CLIENT_TP_ENTRY  A JOIN 
GM_RETAILER B ON A.Clientid=B.Retailerid
 left join  Dhan_Employee sm on  b.SalesPersonId=sm.ID  
       left join  Dhan_Employee ss on  ss.ID=sm.superiorid  
      left join  Dhan_Employee sP on  sP.ID=SS.superiorid  
      left join  Dhan_Employee sN on  sN.ID=SP.superiorid     
   left join  Dhan_Employee sk on sn.superiorid=sk.id   
   where  Deno_2000_count >0 and Posteddatekey >= @STARTDATEkey and Posteddatekey <= @ENDDATEkey
   group by b.RETAILERID,B.CompanyName,sm.emp_Name,ss.emp_Name,sP.emp_Name,sN.emp_Name,sk.emp_Name,	Posteddatekey
union
select b.Distributorid,B.CompanyName,
sm.emp_Name L1,  
    ss.emp_Name L2,  
    sP.emp_Name L3,  
    sN.emp_Name L4,  
    sk.emp_Name L5,
	Posteddatekey ,
	sum(Amount) Toupamount,
	sum(Deno_2000_count) Deno
from CLIENT_TP_ENTRY  A JOIN 
GM_Distributor B ON A.Clientid=B.Distributorid
 left join  Dhan_Employee sm on  b.SalesPersonId=sm.ID  
       left join  Dhan_Employee ss on  ss.ID=sm.superiorid  
      left join  Dhan_Employee sP on  sP.ID=SS.superiorid  
      left join  Dhan_Employee sN on  sN.ID=SP.superiorid     
   left join  Dhan_Employee sk on sn.superiorid=sk.id   
   where  Deno_2000_count >0  and Posteddatekey >= @STARTDATEkey and Posteddatekey <= @ENDDATEkey
    group by b.Distributorid,B.CompanyName,sm.emp_Name,ss.emp_Name,sP.emp_Name,sN.emp_Name,sk.emp_Name,	Posteddatekey

union
select b.Superdistributorid,B.CompanyName,
sm.emp_Name L1,  
    ss.emp_Name L2,  
    sP.emp_Name L3,  
    sN.emp_Name L4,  
    sk.emp_Name L5,
	Posteddatekey,
	sum(Amount) Toupamount,
	sum(Deno_2000_count) Deno
from CLIENT_TP_ENTRY  A JOIN 
GM_SUPERDISTRIBUTOR B ON A.Clientid=B.Superdistributorid
 left join  Dhan_Employee sm on  b.SalesPersonId=sm.ID  
       left join  Dhan_Employee ss on  ss.ID=sm.superiorid  
      left join  Dhan_Employee sP on  sP.ID=SS.superiorid  
      left join  Dhan_Employee sN on  sN.ID=SP.superiorid     
   left join  Dhan_Employee sk on sn.superiorid=sk.id   
   where  Deno_2000_count >0 and Posteddatekey >= @STARTDATEkey and Posteddatekey <= @ENDDATEkey
    group by b.Superdistributorid,B.CompanyName,sm.emp_Name,ss.emp_Name,sP.emp_Name,sN.emp_Name,sk.emp_Name,	Posteddatekey
)  

Select RETAILERID Clientid, CompanyName clientname, L1,L2,L3,L4,L5,Posteddatekey,Toupamount toupupamount,Deno Deno into #TMP_DATEatt from Sales_CTE 

SELECT @pivv=COALESCE(@pivv+',','')+ QUOTENAME(CONVERT(VARCHAR,Posteddatekey)+'_topup')  +','+QUOTENAME(CONVERT(VARCHAR,Posteddatekey)+'_DENOCOUNT') from CLIENT_TP_ENTRY
where  Deno_2000_count >0 and Posteddatekey >= @STARTDATEkey and Posteddatekey <= @ENDDATEkey
GROUP BY Posteddatekey
order by Posteddatekey
 
SET @Columns2 = SUBSTRING((SELECT  ',ISNULL(['+ CONVERT(VARCHAR,Posteddatekey)+'_Topup'+'],''0'') AS ['+CONVERT(VARCHAR,Posteddatekey)+'_Topup'+']',
',ISNULL(['+ CONVERT(VARCHAR,Posteddatekey)+'_Denocount'+'],''0'') AS ['+CONVERT(VARCHAR,Posteddatekey)+'_Denocount'+']' 
FROM CLIENT_TP_ENTRY where  Deno_2000_count >0 and Posteddatekey >= @STARTDATEkey and Posteddatekey <= @ENDDATEkey   
GROUP BY Posteddatekey   order by Posteddatekey FOR XML PATH('')),2,8000)
 
 
SET @Query = N'SELECT Clientid, clientname,' + @Columns2 + ', Total = ' +REPLACE(@pivv, ',[', '+[') + '
FROM
(
SELECT Clientid, clientname,toupupamount,CONVERT(VARCHAR,Posteddatekey)+''_Topup'' Typ  FROM #TMP_DATEatt   
UNION  
SELECT Clientid, clientname,Deno  ,CONVERT(VARCHAR,Posteddatekey)+''_DENOCOUNT'' Typ  FROM #TMP_DATEatt AS P  
) AS j
PIVOT
(
  SUM(toupupamount) FOR  Typ  IN ('+ @pivv + ')
) AS p;';  
 
EXEC sp_executesql @Query;

Answer 1

I see that you are using the PIVOT keyword. That's a dead end if you want a grand-total column - or for that matter anything extra beyond the straightjacket that PIVOT offers.

For what you should use instead and examples with grand total, check out the section on dynamic pivot in my article on dynamic SQL: https://www.sommarskog.se/dynamic_sql.html#pivot

Answer 2

Hi @saravanan d

You could modify the parameter @Columns2 adding SUM to each column. And then add GROUP BY GROUPING SETS((Clientid, clientname),()) in the end of @Query.

I can't modify your code directly because I don't know your table structure. However, you could refer to the sample below:

CREATE TABLE #TBL (UserID INT,Subject VARCHAR(20),Score INT)
INSERT INTO #TBL VALUES
(1,'AAA',76),(1,'BBB',87),(1,'CCC',86),(2,'AAA',98),(2,'BBB',82),(2,'CCC',90)

DECLARE @sql_str VARCHAR(8000)
DECLARE @select_column VARCHAR(8000)
DECLARE @spread_elements VARCHAR(8000)

SELECT @spread_elements = ISNULL(@spread_elements + ',','') + QUOTENAME(Subject) 
FROM #TBL 
GROUP BY Subject
--PRINT @spread_elements
SET @select_column = REPLACE(REPLACE(@spread_elements,'[','SUM(['),']','])')
--PRINT @select_column

SET @sql_str = '
;WITH CTE(UserID,'+@spread_elements+') AS
(
SELECT UserID,'+@select_column+' 
FROM (SELECT UserID,Subject,Score FROM #TBL) S 
PIVOT (MAX(Score) FOR Subject IN ( '+ @spread_elements +') ) AS P
GROUP BY GROUPING SETS((UserID),())
)SELECT * FROM CTE; 
'
--PRINT (@sql_str)
EXEC (@sql_str)

Best regards,

Cosmog Hong

---

If the answer is the right solution, please click "Accept Answer" and kindly upvote it. If you have extra questions about this answer, please click "Comment".

Note: Please follow the steps in our Documentation to enable e-mail notifications if you want to receive the related email notification for this thread.