問題描述

我有這個查詢工作:

      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_ContasPagar 
  group 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;

這篇關于數據透視表 SQL 中的水平總計的文章就介紹到這了，希望我們推薦的答案對大家有所幫助，也希望大家多多支持html5模板網！