Click here to Skip to main content
15,909,518 members
Please Sign up or sign in to vote.
4.00/5 (1 vote)
See more:
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
Posted

1 solution

 
Share this answer
 

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)



CodeProject, 20 Bay Street, 11th Floor Toronto, Ontario, Canada M5J 2N8 +1 (416) 849-8900