您可以对数据透视表中的行和/或列进行小计吗? [英] Can you subtotal rows and/or columns in a pivot table?

问题描述

我有一组查询，它们输出一个数据透视表.是否可以获取数据透视表的行和/或列小计?

I have a set of queries that outputs a pivot table. Is it possible to obtain row and/or column subtotals for a pivot table ?

我要从中选择的桌子如下

My table I am selecting from looks like this

    Site     FormID   Present
    Site 1   Form A      Yes
    Site 1   Form B      Yes
    Site 1   Form D      Yes

等...

我的数据透视表查询是这个

My pivot table query is this

   SELECT *
   FROM (SELECT Site, COUNT(FormID) AS NumberOfForms,FormID
         FROM @CRFCount WHERE Present='Yes'
         GROUP BY Site, FormID) d
   PIVOT
   (SUM(NumberOfForms)
   FOR [Site] IN ([Site 1], [Site 2], [Site 3])
   )  AS p;

但是我真的希望它导致这种情况(当然这对我来说并不总会如此)

But I really want it to result in this (which of course it does not total for me)

    FormID  Site 1  Site 2  Site 3  Total
    Form A      8      8      15    31
    Form B     14      4    NULL    18
    Form C     14   NULL    NULL    14
    Form D     15      3      16    34
    Form E     12      4    NULL    16
    Form F     14      5       5    24
    Form G     14      8       6    28
    Form H     22     10      15    47
    Form I     15     10      16    41
    Form J     15      5      16    36
    Total     143     57      89   289

感谢您的协助！

-唐

推荐答案

;WITH C as
(
  SELECT FormID,
         [Site 1],
         [Site 2],
         [Site 3],
         (SELECT SUM(S)
          FROM (VALUES([Site 1]),
                      ([Site 2]),
                      ([Site 3])) AS T(S)) as Total
   FROM (SELECT Site, COUNT(FormID) AS NumberOfForms,FormID
         FROM @CRFCount WHERE Present='Yes'
         GROUP BY Site, FormID) d
   PIVOT
   (SUM(NumberOfForms)
   FOR [Site] IN ([Site 1], [Site 2], [Site 3])
   )  AS p
)
SELECT *
FROM
  (
    SELECT FormID,
           [Site 1],
           [Site 2],
           [Site 3],
           Total
    FROM C
    UNION ALL
    SELECT 'Total',
           SUM([Site 1]),
           SUM([Site 2]),
           SUM([Site 3]),
           SUM(Total)
    FROM C
  ) AS T
ORDER BY CASE WHEN FormID = 'Total' THEN 1 END

注意::如果您使用的是SQL Server 2005，则需要进行以下更改:

Note: If you are using SQL Server 2005 you need to change this:

 (SELECT SUM(S)
  FROM (VALUES([Site 1]),
              ([Site 2]),
              ([Site 3])) AS T(S)) as Total

到

 (SELECT SUM(S)
  FROM (SELECT [Site 1] UNION ALL
        SELECT [Site 2] UNION ALL
        SELECT [Site 3]) AS T(S)) as Total

尝试 SE数据

这篇关于您可以对数据透视表中的行和/或列进行小计吗?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！