Get count of responses from matrix of survey answers

Question

I have a table that stores responses to well-being questions. The structure is:

Wellbeing(WellbeingId, WellbeingDate, Q1, Q2, Q3, Q4, Q5, Q6, Q7...)

+------------+---------------+----+----+----+
|WellbeingId | WellbeingDate | Q1 | Q2 | Q3 |...
+============+===============+====+====+====+
|1           | 01/01/2015    | 1  | 1  | 5  |
+------------+---------------+----+----+----+
|2           | 10/01/2015    | 3  | 3  | 2  |
+------------+---------------+----+----+----+
|3           | 18/01/2015    | 2  | 4  | 1  |
+------------+---------------+----+----+----+

WellbeingResponses(ResponseId, ResponseText)

+-----------+---------------------+
|ResponseId | ResponseText        |
+===========+=====================+
|1          | 'None of the Time'  |
+-----------+---------------------+
|2          | 'Rarely'            |
+-----------+---------------------+
|3          | 'Sometimes'         |
+-----------+---------------------+
|4          | 'Most of the time'  |
+-----------+---------------------+
|5          | 'All of the time'   |
+-----------+---------------------+

Each Q column has a value between 1 and 5 inclusive. These values are linked to another table for the associated text (ie. 1 = Not very much, 5 = All the time etc.)

I would like to get the data from SQL Server as a total count for each of the possible response values like so:

+-------------------+----------+----------+-------------+
|Response           | Q1 Total | Q2 Total | Q3 Total ...|
+===================+==========+==========+=============+
|'None of the Time' | 500      | 256      | 546         |
+-------------------+----------+----------+-------------+
|'Rarely'           | 500      | 256      | 546         |
+-------------------+----------+----------+-------------+
|'Sometimes'        | 500      | 256      | 546         |
+-------------------+----------+----------+-------------+

I have tried selecting out the individual bits of data and using union all it but that just stacks the data as a count of each of the 5 values from each of the 7 "Q" columns, also tried adapting a pivot query I found on SO somewhere but cant figure that out when trying to use dynamic data for the columns selecting them from the WellbeingResponses table.

Any help would be greatly appreciated.

EDIT : Added well-being responses table.

Answer 1

You've got a bit of a mess with your Wellbeing table. I'd really recommend rewriting this table to be normalized so you can avoid having to perform multiple joins or even unpivoting the data to get the desired result you want.

Since your current table is denormalized, you've got to convert the data to make it workable, then aggregate it, finally repivot it into your final desired result. It's going to be a bit of a mess but there are several ways that you can do this.

One way that you can get the result is to unpivot your wellbeing table so the data is normalized. Since you are using SQL Server you can use the UNPIVOT function or, depending on version, you can use CROSS APPLY. The code to convert your multiple columns into multiple rows will be:

select col, value
from wellbeing
cross apply
(
  select 'Q1', Q1 union all
  select 'Q2', Q2 union all
  select 'Q3', Q3
) c (col, value);

See Demo. This gets your data in the format:

| COL | VALUE |
|-----|-------|
|  Q1 |     1 |
|  Q2 |     1 |
|  Q3 |     5 |
|  Q1 |     3 |
|  Q2 |     3 |

Now the data can be easily joined on with your other table:

select r.ResponseText, d.col
from WellbeingResponses r
left join
(
  select col, value
  from wellbeing
  cross apply
  (
    select 'Q1', Q1 union all
    select 'Q2', Q2 union all
    select 'Q3', Q3
  ) c (col, value)
) d
  on r.responseid = d.value

See Demo. Once you've got a list of each question and the response, you can aggregate it and pivot the totals:

select ResponseText, q1, q2, q3
from
(
  select r.ResponseText, d.col
  from WellbeingResponses r
  left join
  (
    select col, value
    from wellbeing
    cross apply
    (
      select 'Q1', Q1 union all
      select 'Q2', Q2 union all
      select 'Q3', Q3
    ) c (col, value)
  ) d
    on r.responseid = d.value
) s
pivot
(
  count(col)
  for col in (Q1, Q2, Q3)
) piv

See SQL Fiddle with Demo.

An alternative way to get the result would be to perform multiple joins on your wellbeing table. Each join would be on the question column:

select r.responsetext,
  Q1Total = count(w1.q1),
  Q2Total = count(w2.q2),
  Q3Total = count(w3.q3)
from WellbeingResponses r
left join wellbeing w1
  on r.responseid = w1.q1
left join wellbeing w2
  on r.responseid = w2.q2
left join wellbeing w3
  on r.responseid = w3.q3
group by r.responsetext;

See Demo. Both versions will give a result:

|     RESPONSETEXT | Q1TOTAL | Q2TOTAL | Q3TOTAL |
|------------------|---------|---------|---------|
|  All of the time |       0 |       0 |       1 |
| Most of the time |       0 |       1 |       0 |
| None of the Time |       1 |       1 |       1 |
|           Rarely |       1 |       0 |       1 |
|        Sometimes |       1 |       1 |       0 |