带计数和求和的SQL Server数据透视表 [英] SQL Server Pivot Table with Counts and Sums
问题描述
我正在尝试使一个SQL Server Pivot表能够工作,该表允许我进行计数,然后求和一些列(总计6列).数据透视表的目的是汇总任何数量的生产站点的在线问卷调查结果.有6个问题,可以有3个结果值-目标,操作和失败.我想做的是计算每个问题的目标,操作和失败的数量,然后对每个问题求和.因此,例如,生产站点A可能有2个目标,2个操作和2个失败.
I am trying to get an SQL Server Pivot table to work that allows me to count and then sum a number of columns (6 in total). The purpose of the pivot table is to aggregate online questionnaire results for any number of production sites. There are 6 questions which can have 3 result values - Target, Action and Fail. What I am trying to do is count up the number of Target, Action and Fail for each question, and to then sum this up for each. So, for example, Production Site A could have 2 Target, 2 Action and 2 Fail.
我的理解是SQL Server Pivot表可以提供此信息,然后可以在ASP.Net ReportViewer中显示该信息.下面是我的代码,但是它不起作用,并且可以在一些专家帮助下完成:
My understanding is that the SQL Server Pivot table could deliver this information, which can then be display in ASP.Net ReportViewer. Below is my code, but it is not working, and could do with some expert help:
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
有没有解决的办法,还是数据透视表不是解决方案?
Is there a way round this, or is Pivot table not the solution?
表是
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
推荐答案
执行此查询更简单的方法是同时应用UNPIVOT
和PIVOT
函数:
A much easier way to perform this query would be to apply both the UNPIVOT
and then the PIVOT
functions:
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小提琴
UNPIVOT
获取您的列列表并将其转换为多行,这使计数变得更加容易:
The UNPIVOT
takes your column list and transform it into multiple rows which makes it much easier to count:
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
后的结果是:
Then applying the PIVOT
to this will get the count that you want for each of the PRODUCTION_SITE
s. After adding the PIVOT
the result is:
| PRODUCTION_SITE | TARGET | ACTION | FAIL |
--------------------------------------------
| Site A | 10 | 1 | 1 |
| Site B | 4 | 1 | 1 |
| Site C | 6 | 0 | 0 |
这篇关于带计数和求和的SQL Server数据透视表的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!