SQL Server数据透视表：计数和求和

Question

SQL Server数据透视表：计数和求和

10

我正在尝试创建一个SQL Server透视表，以便对多个列（共6列）进行计数和求和。透视表的目的是汇总任意数量的生产站点的在线问卷结果。有6个问题，每个问题都有3个结果值 - 目标(Target)、行动(Action)和失败(Fail)。我正在尝试对每个问题的目标(Target)、行动(Action)和失败(Fail)进行计数，并对其进行求和。例如，Production Site A可能有2个目标(Target)、2个行动(Action)和2个失败(Fail)。

据我了解，SQL Server透视表可以提供这些信息，然后可以在ASP.Net ReportViewer中显示。以下是我的代码，但它没有起作用，需要专家帮助：

     SELECT PRODUCTION_Site,
     [Target],
     [Action],
     [Fail]
     FROM
     (SELECT Production_Site,
             SUM(Coding),
             SUM(Measurable),
             SUM(Appearance),
             SUM(Aroma),
             SUM(Flavour),
             SUM(Texture)
               FROM t_Pqe_Grocery
               GROUP BY Production_Site) AS T
          PIVOT
         (
           COUNT(Coding) FOR Grocery_Packaging_And_Coding IN ([Target],[Action],[Fail])
           COUNT(Measurable) FOR Grocery_Measurable IN ([Target],[Action],[Fail])
           COUNT(Appearance) FOR Grocery_Appearance IN ([Target],[Action],[Fail])
           COUNT(Aroma) FOR Grocery_Aroma IN ([Target],[Action],[Fail])
           COUNT(Flavour) FOR Grocery_Flavour IN ([Target],[Action],[Fail])
           COUNT(Texture) FOR Grocery_Texture IN ([Target],[Action],[Fail])) AS P

有没有其他解决方案，或者数据透视表不是解决方案？

表格是：

Production_Site,
Grocery_Packaging_And_Coding,
Grocery_Measurable,
Grocery_Appearance,
Grocery_Aroma,
Grocery_Flavour,
Grocery_Texture

表格中的数据应该是这样的：

Site A, Target, Action, Fail, Target, Target, Target
Site B, Target, Action, Fail, Target, Target, Target
Site C, Target, Target, Target, Target, Target, Target
Site A, Target, Target, Target, Target, Target, Target

我要寻找的结果是：

Production_Site | Target | Action | Fail
Site A              10       1       1
Site B               4       1       1
Site C               6       0       0

- Andy5

你能提供表结构、一些样本数据以及 PIVOT 的期望结果吗？ - Taryn

我将保留这个主要数据 - 有关详细信息请查看原始问题。 - Andy5

什么不起作用？你能设置一个 SQL Fiddle 示例吗？ - Dominic Goulet

刚刚添加了一些表结构、数据以及我试图实现的结果示例。 - Andy5

1个回答

网页内容由stack overflow 提供, 点击上面的

可以查看英文原文，
原文链接

- Taryn · Accepted Answer

执行此查询的更简单方法是应用UNPIVOT和PIVOT函数：

select *
from
(
  select Production_Site, value 
  from t_Pqe_Grocery
  unpivot
  (
    value
    for col in (Grocery_Packaging_And_Coding, Grocery_Measurable,
                Grocery_Appearance, Grocery_Aroma, 
                Grocery_Flavour, Grocery_Texture)
  ) unp
) src
pivot
(
  count(value)
  for value in ([Target], [Action], [Fail])
) piv

请查看带有演示的SQL Fiddle

UNPIVOT将您的列列表转换为多行，从而使计数更加容易:

select Production_Site, value 
from t_Pqe_Grocery
unpivot
(
  value
  for col in (Grocery_Packaging_And_Coding, Grocery_Measurable,
              Grocery_Appearance, Grocery_Aroma, 
              Grocery_Flavour, Grocery_Texture)
) unp

转置结果：

| PRODUCTION_SITE |  VALUE |
----------------------------
|          Site A | Target |
|          Site A | Action |
|          Site A |   Fail |
|          Site A | Target |
|          Site A | Target |
|          Site A | Target |
|          Site B | Target |
|          Site B | Action |
|          Site B |   Fail |
|          Site B | Target |
|          Site B | Target |
|          Site B | Target |
|          Site C | Target |
|          Site C | Target |
|          Site C | Target |
|          Site C | Target |
|          Site C | Target |
|          Site C | Target |
|          Site A | Target |
|          Site A | Target |
|          Site A | Target |
|          Site A | Target |
|          Site A | Target |
|          Site A | Target |

然后将 PIVOT 应用于此，将获得每个 PRODUCTION_SITE 的所需计数。添加 PIVOT 后的结果如下：

| PRODUCTION_SITE | TARGET | ACTION | FAIL |
--------------------------------------------
|          Site A |     10 |      1 |    1 |
|          Site B |      4 |      1 |    1 |
|          Site C |      6 |      0 |    0 |