SQL Server Pivot Table with Counts and Sums

Question

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.

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?

Table is

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

Data in the table would like this:

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

The result I am looking for is

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

can you provide the table structure, some sample data and then the desired result of the `PIVOT`? — Taryn, Nov 16 '12 at 12:27
I keep this to the main data - check original question for details — Andy5, Nov 16 '12 at 13:02
Just added some table structure, data and an example of the result I am trying to acheive — Andy5, Nov 16 '12 at 13:10

score 14 · Accepted Answer · answered Nov 16 '12 at 13:13

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

See SQL Fiddle with Demo

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

Unpivot 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 the PIVOT to this will get the count that you want for each of the PRODUCTION_SITEs. 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 |

Thank you, but one last question is it possible for example to add a where clause to the pivot if a user wants to look at say 3 sites rather than all sites over a time period? — Andy5, Nov 16 '12 at 13:20
@Andy5 yes, you can still apply a `WHERE` clause to limit any of the results - similar to this -- http://sqlfiddle.com/#!3/cd888/23 -- you can even apply a `WHERE` clause to the inner `UNPIVOT` query — Taryn, Nov 16 '12 at 13:22

SQL Server Pivot Table with Counts and Sums

1 Answers1

Linked