找到具有不同标准的总和的多列 [英] Multiple columns finding the sum which has different criteria

问题描述

名称AsiaPacific-F& A EALA-F& A EALA-HA

已部署薪资44.7 2,565.19 86.38

净（贷款）/借款薪资2,537.68 13,532.05 21.32



i在sql查询中使用pivot得到了上面的表格。

现在我需要根据有两个标准的列标题来总结。



这应该显示如下



名称AsiaPacific-F& A TotalA EALA-F& A EALA-HA TotalB

已部署的工资单44.7 44.7 2,565.19 86.38 2651.57

净额（贷款）/借款工资2,537.68 2,537.68 13,532.05 21.32 13553.37



这里TotalA =总和（AsiaPacific-F& A）

和TotalB =总和（EALA-F& A EALA-HA）



请帮帮我。

提前致谢



我尝试过：



选择姓名，[亚太地区ic-财务和会计]，[EALA-财务和会计]，[EALA-健康管理]来自

（

选择名称，Value1，ROW_NUMBER（）结束（分区）通过[地理区域]，[商业服务]订购[地理区域]）

作为Row1，[地理区域] +' - '+ [商业服务]作为第1列来自#PivotUnpivotFinal

）

作为查询

PIVOT（MAX（Value1）

FOR column1 IN（[Asia Pacific-Finance and Accounting]， [EALA-财务和会计]，[EALA-健康管理]））AS Pivot1

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

推荐答案

查看我的文章 [ ^ ]。它有一章关于求和

See my Article Dynamic Pivoting with Cubes and eventhandlers in SQL Server 2005, 2008 and 2008 R2[^]. it has a chapter about summing

这篇关于找到具有不同标准的总和的多列的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！