CODEWITHSUNDEEP
sqlsql-serverpivotunpivot
Richard
Richard

Reputation: 13

SQL Server Pivot table for survey responses

I have a sql server table called surveys with the following data

+------------+--------------+----+----+----+----+
| ModuleCode | SurveyNumber | Q1 | Q2 | Q3 | Q4 |
+------------+--------------+----+----+----+----+
| NME3519    |            1 |  5 |  4 |  5 |  3 |
| NME3519    |            2 |  3 |  3 |  2 |  1 |
| NME3519    |            3 |  4 |  3 |  2 |  1 |
| NME3520    |            1 |  4 |  3 |  2 |  1 |
| NME3519    |            4 |  4 |  2 |  2 |  1 |
+------------+--------------+----+----+----+----+

I'd like to be able to report on one module at a time and for the result to be something like this:

            Count of scores
+----------+---+---+---+---+---+
| Question | 1 | 2 | 3 | 4 | 5 |
+----------+---+---+---+---+---+
| Q1       | 0 | 0 | 1 | 2 | 1 |
| Q2       | 0 | 1 | 2 | 1 | 0 |
| Q3       | 0 | 3 | 0 | 0 | 1 |
| Q4       | 3 | 0 | 1 | 0 | 0 |
+----------+---+---+---+---+---+

I'm pretty sure from other examples I need to unpivot and then pivot but I can't get anywhere with my own data.

Many thanks

Richard

Upvotes: 0

Views: 144

Answers (1)

Gordon Linoff
Gordon Linoff

Reputation: 1270431

Unpivot and aggregate:

select v.question,
       sum(case when v.score = 1 then 1 else 0 end) as score_1,
       sum(case when v.score = 2 then 1 else 0 end) as score_2,
       sum(case when v.score = 3 then 1 else 0 end) as score_3,
       sum(case when v.score = 4 then 1 else 0 end) as score_4,
       sum(case when v.score = 5 then 1 else 0 end) as score_5
from responses r cross apply
     ( values ('Q1', r.q1), ('Q2', r.q2), ('Q3', r.q3), ('Q4', r.q4), ('Q5', r.q5)
     ) v(question, score)
group by v.question;

This version uses a lateral join for unpivoting. I find the syntax simpler and lateral joins more powerful. Why bother learning unpivot when something else does the same thing more concisely, more powerfully, and has the same performance?

As for the pivoting, it uses conditional aggregation. In my experience with SQL Server, this has pretty much the same performance as pivot.

Upvotes: 1

Related Questions