Horizontal Grand Total in Pivot Table SQL

sql, sql-server, sql-server-2008, t-sql

Solution

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;

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;

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;

Problem

I have this query working: ``` 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; ``` This query returns: ``` 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 ``` 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 ``` How to make this? I have read something with `UNION`.

Original source