Name AsiaPacific-F&A EALA-F&A EALA-HA
Deployed Payroll 44.7 2,565.19 86.38
Net (Loan)/Borrow Payroll 2,537.68 13,532.05 21.32
i have got this above table using pivot in sql query.
now i need to sum up based on columnheading which has 2 criteria.
this should display like this
Name AsiaPacific-F&A TotalA EALA-F&A EALA-HA TotalB
Deployed Payroll 44.7 44.7 2,565.19 86.38 2651.57
Net (Loan)/Borrow Payroll 2,537.68 2,537.68 13,532.05 21.32 13553.37
here the TotalA= sum(AsiaPacific-F&A)
and the TotalB=sum( EALA-F&A EALA-HA )
Please help me.
Thanks in advance
What I have tried:
Select Name,[Asia Pacific-Finance and Accounting],[EALA-Finance and Accounting],[EALA-Health Administration] FROM
(
select Name,Value1,ROW_NUMBER() Over (Partition by [Geo Area],[Business Services] order by [Geo Area])
as Row1,[Geo Area]+'-'+[Business Services] as column1 from #PivotUnpivotFinal
)
As query
PIVOT (MAX (Value1)
FOR column1 IN ([Asia Pacific-Finance and Accounting],[EALA-Finance and Accounting],[EALA-Health Administration])) AS Pivot1