sql-server - 您可以对数据透视表中的行和/或列进行小计吗？

Question

我有一组输出数据透视表的查询。是否可以获得数据透视表的行和/或列小计？

我选择的表格看起来像这样

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

等等...

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

   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;

但我真的希望它能带来这样的结果(当然这对我来说并不完全)

    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

感谢您的帮助!

-唐

Answer 1

;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，则需要更改此设置:

 (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 Data