我正在尝试创建一个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中显示。以下是我的代码,但它没有起作用,需要专家帮助:
表格中的数据应该是这样的:
我要寻找的结果是:
据我了解,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
PIVOT
的期望结果吗? - Taryn