Search code examples
sqlsql-serversql-server-2014

SQL Query to parse survey results

First off, I have attempted to search for an answer, but honestly don't know exactly what to search for...

I have a dataset I've imported into SQL Server and need to get usable data from it. Here's the format...

Teacher     Period    Question1    Question2    Question3
Jane Doe    1         Agree        Agree        Strongly Agree
Jane Doe    2         Disagree     Agree        Agree
John Doe    2         Agree        Disagree     Agree
John Doe    4         Disagree     Agree        Strongly Disagree
Jane Doe    3         Agree        Agree        Agree

and so on... What I need is to be able to return a count of Jane Doe's different answers for Question1, Question2, etc. I need to return for Jane's Question1, there are 2 Agrees and 1 Disagree. I need this for multiple teachers.

I have tried using GROUP BY but I must not be doing something right. I've tried variations of:

SELECT Teacher, COUNT(Q1) AS Q1Result, COUNT(Q2) AS Q2Result
FROM Survey
GROUP BY Teacher, Q1, Q2

Thanks for your help!

EDIT

So, this is the query that ended up getting me what I needed. My data needed to be unpivoted I believe to make this much easier. This is the query that got me what I was looking for...

SELECT s.Teacher,
       q.Question,
       COUNT(CASE WHEN val = 'Strongly Agree' THEN 1 END) StronglyAgreeCount,
       COUNT(CASE WHEN val = 'Agree' THEN 1 END) AgreeCount,
       COUNT(CASE WHEN val = 'Neutral' THEN 1 END) NeutralCount,
       COUNT(CASE WHEN val = 'Disagree' THEN 1 END) DisagreeCount,
       COUNT(CASE WHEN val = 'Strongly Disagree' THEN 1 END)     StronglyDisagreeCount
FROM   PCSSSurvey s 
       CROSS APPLY (VALUES(s.Q1, 'Question01'),(s.Q2, 'Question02'),(s.Q3, 'Question03'),(s.Q4, 'Question04'),(s.Q5, 'Question05'),(s.Q6, 'Question06'),(s.Q7, 'Question07'),(s.Q8, 'Question08'),(s.Q9, 'Question09'),(s.Q10, 'Question10'),(s.Q11, 'Question11'),(s.Q12, 'Question12'),(s.Q13, 'Question13')) q(val,question)
GROUP BY s.Teacher, q.Question
ORDER BY s.Teacher, q.Question
Solution

  • You could do something like this to get counts for all options per question.

    SELECT s.Teacher,
       q.Question,
       COUNT(CASE WHEN val = 'Agree' THEN 1 END) AgreeCount,
       COUNT(CASE WHEN val = 'Strongly Agree' THEN 1 END) StronglyAgreeCount,
       COUNT(CASE WHEN val = 'Disagree' THEN 1 END) DisagreeCount,
       COUNT(CASE WHEN val = 'Strongly Disagree' THEN 1 END) StronglyDisagreeCount
FROM   Survey s 
       CROSS APPLY (VALUES(s.Question1, 'Question1'),(s.Question2, 'Question2'),(s.Question3, 'Question3')) q(val,question)
GROUP BY s.Teacher, q.Question
ORDER BY s.Teacher, q.Question

ouput:
Teacher  Question  AgreeCount  StronglyAgreeCount DisagreeCount StronglyDisagreeCount
-------- --------- ----------- ------------------ ------------- ---------------------
Jane Doe Question1 2           0                  1             0
Jane Doe Question2 3           0                  0             0
Jane Doe Question3 2           1                  0             0
John Doe Question1 1           0                  1             0
John Doe Question2 1           0                  1             0
John Doe Question3 1           0                  0             1