Geek Answers Handbook

SQL Server PivotTable Summary

I am trying to get a SQL Server pivot table table to work, which allows me to count and then summarize multiple columns (total 6). The purpose of the pivot table is to combine the results of an online survey for any number of production sites. There are 6 questions that can have 3 result values โ€‹โ€‹- Target, Action and Fail. What I'm trying to do is count the number of goals, actions, and failures for each question, and then summarize them for each. So, for example, on production site A there can be 2 Target objects, 2 Action and 2 Fail.

I understand that a SQL Server pivot table can provide this information, which can then be displayed in ASP.Net ReportViewer. Below is my code, but it does not work and may help with some 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 around this, or is this pivot table not a solution?

Table

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

The data in the table:

 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

As a result, I seek

 Production_Site | Target | Action | Fail Site A 10 1 1 Site B 4 1 1 Site C 6 0 0
+6
source share
1 answer

An easier way to execute this query is to use both the UNPIVOT and 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

See SQL Fiddle with Demo

UNPIVOT displays a list of columns and converts it into several rows, which greatly facilitates the calculation:

 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

Unique result:

 | 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 |

Then applying PIVOT to this will result in the count you want for each of PRODUCTION_SITE s. After adding the PIVOT result:

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

More articles:


All Articles