SUM individual dynamic columns from pivot table -SQL server/SAP B1

Question

I writing a SQL server query for based on an SAP Business One DB, I have a dynamic list of columns that I am using in a PIVOT to create dynamic columns.im currently getting separate data since I pass though the "PrcName" into the group by for the Pivot. I want to be able to sum each dynamic column in my #TEMP table to SUMing the individual "PrcName" Rows. Something lie this: SUM(' + @Columns + ')

The below query gives me something like this:

Type    FatherAccount   levels  AcctCode    AccntntCod  AcctName    Group1  Group2   Project1   Project2
Expense Cost    11  1000002 400002  Resource    0   0   1000    0
Expense Cost    11  1000002 400002  Resource    0   0   0   2000

But I trying to sum them into this:

Type    FatherAccount   levels  AcctCode    AccntntCod  AcctName    Group1  Group2   Project1   Project2
Expense Cost    11  1000002 400002  Resource    0   0   1000    2000

SQL query:

SELECT * INTO #TEMP FROM(


SELECT Type, FatherAccount, Levels, [AcctCode], [AccntntCod], [AcctName], [Tender], [OverHead], ' + @Columns + '
FROM
(SELECT  
CASE WHEN T0.GroupMask IN (''4'') THEN ''Revenue'' ELSE ''Expense'' END AS "Type", 
T5.AcctName AS "FatherAccount", ''11'' AS Levels, T0.[AcctCode], T0.[AccntntCod], T0.[AcctName],
CASE WHEN T2.PrcName LIKE ''%Group1%'' THEN SUM(T1.[Debit] - T1.[Credit])END AS Group1",
CASE WHEN T2.PrcName LIKE ''%Group2%'' THEN SUM(T1.[Debit] - T1.[Credit])END AS "Group1",
SUM(T1.[Debit] - T1.[Credit]) AS Balance, 

T2.[PrcName] AS PrcName
,ROW_NUMBER() OVER(PARTITION BY T0.AcctName, SUM(T1.[Debit] - T1.[Credit]) ORDER BY T0.AcctCode DESC) rn
FROM OACT T0  
LEFT JOIN JDT1 T1 ON T0.[AcctCode] = T1.[Account]
LEFT JOIN OPRC T2 ON T2.PrcCode = T1.ProfitCode
LEFT JOIN OPRC T3 ON T3.PrcCode = T1.OcrCode2
LEFT JOIN OPRC T4 ON T4.PrcCode = T1.OcrCode3
LEFT JOIN OACT T5 ON T5.AcctCode = T0.FatherNum

WHERE T0.GroupMask IN (''4'', ''5'', ''6'', ''7'') AND (T0.Postable = ''Y'')
AND ((T1.RefDate Between ''[%0]'' AND ''[%1]'') OR T0.CurrTotal = 0)
AND (T4.PrcName = ''[%2]'' OR T0.CurrTotal = 0)
--GROUP BY GROUPING SETS ((T0.Levels,T0.[AcctCode],T0.[AccntntCod], T0.[AcctName], T5.AcctName, T2.PrcName, T0.GroupMask),(T5.AcctName))
--GROUP BY T0.Levels,T0.[AcctCode],T0.[AccntntCod], T0.[AcctName], T5.AcctName, T2.PrcName, T0.GroupMask
) as PivotData

PIVOT
(
   SUM(Balance)
   FOR PrcName IN (' + @Columns + ')
) AS PivotResult
) AS #TEMP



SELECT T0.Type, T0.FatherAccount, T0.levels, T0.AcctCode, T0.AccntntCod, T0.AcctName, SUM(T0.Group1) AS Group1, SUM(T0.Group2) AS Group1, SUM(' + @Columns + ')

FROM #TEMP T0 
GROUP BY T0.Type, T0.FatherAccount, T0.levels, T0.AcctCode, T0.AccntntCod, T0.AcctName, ' + @Columns + '

ORDER BY Type, Levels, FatherAccount

Thanks for the help!

Answer 1

Without adding the values from the PrcName column to the pivoted columns, does this query return correct output?

SELECT CASE WHEN T0.GroupMask IN ('4') THEN 'Revenue' ELSE 'Expense' END AS [Type], 
       T5.AcctName AS FatherAccount,
       '11' AS Levels,
       T0.[AcctCode],
       T0.[AccntntCod],
       T0.[AcctName],
       T2.[PrcName] AS PrcName,
       sum(CASE WHEN T2.PrcName LIKE '%Group1%' THEN (T1.[Debit] - T1.[Credit]) else 0 END) AS Group1,
       sum(CASE WHEN T2.PrcName LIKE '%Group2%' THEN (T1.[Debit] - T1.[Credit]) else 0 END) AS Group2,
       SUM(T1.[Debit] - T1.[Credit]) AS Balance
FROM OACT T0  
     LEFT JOIN JDT1 T1 ON T0.[AcctCode] = T1.[Account]
     LEFT JOIN OPRC T2 ON T2.PrcCode = T1.ProfitCode
     LEFT JOIN OPRC T3 ON T3.PrcCode = T1.OcrCode2
     LEFT JOIN OPRC T4 ON T4.PrcCode = T1.OcrCode3
     LEFT JOIN OACT T5 ON T5.AcctCode = T0.FatherNum
WHERE T0.GroupMask IN ('4', '5', '6', '7') 
      AND (T0.Postable = 'Y')
      AND ((T1.RefDate Between '[%0]' AND '[%1]') OR T0.CurrTotal = 0)
      AND (T4.PrcName = '[%2]' OR T0.CurrTotal = 0)
GROUP BY [Type], T5.AcctName, T0.[AcctCode], T0.[AccntntCod], T0.[AcctName], T2.PrcName;