在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和 环境名称一栏包含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

我已经准备好报告，直到

I have prepare the report till

    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.

上述方法中的第一种，即获取所有数据后的总和，可以非常简单的方式完成，在下面的实现中对您来说唯一可能是新颖的事情可能是

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()部分会为您生成总计 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:

• 方法1

方法2

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

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