问题描述
我有这个查询工作:
select cap_idPlanoContasFin , [3684],[2234],[2] , from ( select cap_idPlanoContasFin,cap_idempresa,sum(cap_valorfatura) as Stotal from erp_ContasPagar group by cap_idPlanoContasFin , cap_idEmpresa ) as sourcetable pivot (sum(Stotal)for cap_idEmpresa in ([3684],[2234],[2]) )as pivottable;
此查询返回:
cap_idPlanoContasFin 3684 2234 2 3 9000 NULL NULL 10 1057840,68 NULL 1865081,35 11 NULL 7283,1 591,9 12 NULL NULL 178914,45 13 9305,07 1117,6 500 14 NULL 59333,5 34611,74
我想在同一个查询中放入 Horizo??ntal Total示例:
I want to put in the same query the Horizontal Total Example:
cap_idPlanoContasFin 3684 2234 2 Total --------------------------------------------------------------------- 13 9305,07 1117,6 500 10922,67
这个怎么做?我用 UNION 读过一些东西.
How to make this? I have read something with UNION.
推荐答案
首先,您不需要事先对数据进行分组:PIVOT 子句会为您做到这一点.因此,您可以删除 GROUP BY 子句并相应地更改 PIVOT 中 SUM() 的参数:
First of all, you don't need to group your data beforehand: the PIVOT clause will do that for you. So you can remove the GROUP BY clause and change the SUM()'s argument in PIVOT accordingly:
select cap_idPlanoContasFin, [3684], [2234], [2] from ( select cap_idPlanoContasFin, cap_idempresa, cap_valorfatura from erp_ContasPagargroup by cap_idPlanoContasFin , cap_idEmpresa) as sourcetable pivot ( sum(cap_valorfatura) for cap_idEmpresa in ([3684], [2234], [2]) ) as pivottable;
要添加总计列,您可以使用 window SUM() 像这样:
To add a total column, you could use a window SUM() like this:
select cap_idPlanoContasFin, [3684], [2234], [2], Total from ( select cap_idPlanoContasFin, cap_idempresa, cap_valorfatura, sum(cap_valorfatura) over (partition by cap_idPlanoContasFin) as Total from erp_ContasPagar ) as sourcetable pivot ( sum(cap_valorfatura) for cap_idEmpresa in ([3684], [2234], [2]) ) as pivottable;
但是请注意,如果您的 sourcetable 包含的行的 cap_idEmpresa 值不是 PIVOT 子句中列出的值,则相应的 cap_valorfatura 值也会加起来.因此,您可能希望在旋转之前过滤 sourcetable 行集,如下所示:
Note, however, that if your sourcetable includes rows with cap_idEmpresa values other than those listed in the PIVOT clause, the corresponding cap_valorfatura values will be added up too. So you might want to filter the sourcetable row set before pivoting, like this:
select cap_idPlanoContasFin, [3684], [2234], [2], Total from ( select cap_idPlanoContasFin, cap_idempresa, cap_valorfatura, sum(cap_valorfatura) over (partition by cap_idPlanoContasFin) as Total from erp_ContasPagar where cap_idempresa in (3684, 2234, 2) ) as sourcetable pivot ( sum(cap_valorfatura) for cap_idEmpresa in ([3684], [2234], [2]) ) as pivottable;