在 sql server 2008 中使用带有列和行总计的数据透视表 [英] Using pivot table with column and row totals in sql server 2008

问题描述

我有一个包含以下列的表格

I have a table with following columns

defect_id, developer_name, status, summary, root_cause, 
Secondary_RC, description, Comments, environment_name

root_cause 列的值是 Enviro、Requi、Dev、TSc、TD、Unkn，并且列 environment_name 有 QA1、QA2、QA3

The column root_cause has Enviro, Requi, Dev, TSc, TD, Unkn as its values and column environment_name has QA1, QA2, QA3

我需要准备一份如下格式的报告

I need to prepare a report in the below format

    Enviro Requi  Dev TSc  TD Unkn  Total
QA1    9    1     14   17   2   3   46
QA2    8    1     14   0    5   1   29
QA3    1    1      7   0    0   1   10
Total 18    3     35   17   7   5   85

我已经准备好报告了

    Enviro Requi  Dev  TSc  TD Unkn 
QA1    9    1     14    17   2  3   
QA2    8    1     14    0    5  1   
QA3    1    1      7    0    0  1   

我使用下面的查询得到了上面的结果

I used the below query to get the above result

select *
from
(
  select environment_name as " ", value
  from test1 
  unpivot
  (
     value
    for col in (root_cause)
  ) unp
) src
pivot
(
  count(value)
  for value in ([Enviro] , [Requi] , [Dev] , [Tsc], [TD] , [Unkn])
) piv

任何人都可以帮助获取列和行的总数吗?

Can anyone help to get the totals for columns and rows?

推荐答案

可能有多种解决方案.您可以在透视后计算所有总计，也可以先获取总计，然后透视所有结果.也有可能有一种中间立场:获取一种总计(例如，按行计算)，枢轴，然后获取另一种，尽管这可能有点过头了.

There may be various approaches to this. You can calculate all the totals after the pivot, or you can get the totals first, then pivot all the results. It is also possible to have kind of middle ground: get one kind of the totals (e.g. the row-wise ones), pivot, then get the other kind, although that might be overdoing it.

第一个提到的方法，在枢轴之后得到所有的总数，可以以一种非常简单的方式完成，在下面的实现中，唯一对你来说可能是新的可能是 GROUP BY ROLLUP():

The first of the mentioned approaches, getting all the totals after the pivot, could be done in a very straightforward way, and the only thing potentially new to you in the below implementation might be GROUP BY ROLLUP():

SELECT
  [ ]      = ISNULL(environment_name, 'Total'),
  [Enviro] = SUM([Enviro]),
  [Requi]  = SUM([Requi]),
  [Dev]    = SUM([Dev]),
  [Tsc]    = SUM([Tsc]),
  [TD]     = SUM([TD]),
  [Unkn]   = SUM([Unkn]),
  Total    = SUM([Enviro] + [Requi] + [Dev] + [Tsc] + [TD] + [Unkn])
FROM (
  SELECT environment_name, root_cause
  FROM test1
) s
PIVOT (
  COUNT(root_cause)
  FOR root_cause IN ([Enviro], [Requi], [Dev], [Tsc], [TD], [Unkn])
) p
GROUP BY
  ROLLUP(environment_name)
;

基本上，GROUP BY ROLLUP() 部分会为您生成 Total row.分组首先由 environment_name 完成，然后添加总计行.

Basically, the GROUP BY ROLLUP() part produces the Total row for you. The grouping is first done by environment_name, then the grand total row is added.

反之亦然，即在旋转之前获取总数，您可以像这样使用 GROUP BY CUBE():

To do just the opposite, i.e. get the totals prior to pivoting, you could employ GROUP BY CUBE() like this:

SELECT
  [ ]      = environment_name,
  [Enviro] = ISNULL([Enviro], 0),
  [Requi]  = ISNULL([Requi] , 0),
  [Dev]    = ISNULL([Dev]   , 0),
  [Tsc]    = ISNULL([Tsc]   , 0),
  [TD]     = ISNULL([TD]    , 0),
  [Unkn]   = ISNULL([Unkn]  , 0),
  Total    = ISNULL(Total   , 0)
FROM (
  SELECT
    environment_name = ISNULL(environment_name, 'Total'),
    root_cause       = ISNULL(root_cause,       'Total'),
    cnt              = COUNT(*)
  FROM test1
  WHERE root_cause IS NOT NULL
  GROUP BY
    CUBE(environment_name, root_cause)
) s
PIVOT (
  SUM(cnt)
  FOR root_cause IN ([Enviro], [Requi], [Dev], [Tsc], [TD], [Unkn], Total)
) p
;

这两种方法都可以在 SQL Fiddle 进行测试和使用:

Both methods can be tested and played with at SQL Fiddle:

• 方法一

方法二

注意.我在这两个建议中都省略了反透视步骤，因为反透视单个列似乎显然是多余的.不过，如果还有更多内容，调整其中一个查询应该很容易.

这篇关于在 sql server 2008 中使用带有列和行总计的数据透视表的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！