Another use of case

This query counts the responses on two different test sessions, and gives a column with the number of responses for an answer on the pre, and on the post, all in one row of the results.

SELECT e.SequenceNum, d.QuestionID, d.QuestionText, z.Sort, z.AnswerText, 
COUNT(CASE WHEN b.InstanceID = 1041 THEN z.AnswerText END) AS PreCount, 
COUNT(CASE WHEN b.InstanceID = 1058 THEN z.AnswerText END) AS PostCount

FROM Answers z RIGHT OUTER JOIN UserResponses_Answers a ON z.AnswerID = a.AnswerID
LEFT OUTER JOIN UserResponses b ON a.ResponseID = b.ResponseID
LEFT OUTER JOIN tblUsers c ON b.UserID = c.numUserID
LEFT OUTER JOIN Questions d ON b.QuestionID = d.QuestionID
LEFT OUTER JOIN TestQuestionXREF e ON d.QuestionID = e.QuestionID

WHERE b.InstanceID IN (1041, 1058)
and c.numUserID IN (
   SELECT UserID
   FROM InstanceUserXREF
     WHERE InstanceID IN (1045)
)

GROUP BY e.SequenceNum, d.QuestionID, d.QuestionText, z.Sort, z.AnswerText
ORDER BY e.SequenceNum

Some example rows that came from this query:

SequenceNum   QuestionID   QuestionText   Sort   AnswerText   PreCount   PostCount

2    9208    Choose the response that best reflects your actual teaching practice (not what you think would be best or that we want to hear) so that changes, if any, can be detected when the survey is retaken.     2    Teacher is center of lesson, sometimes acts as facilitator.    3    3
2    9208    Choose the response that best reflects your actual teaching practice (not what you think would be best or that we want to hear) so that changes, if any, can be detected when the survey is retaken.     3    Teacher is center of lesson, frequently acts as facilitator.    11    4
2    9208    Choose the response that best reflects your actual teaching practice (not what you think would be best or that we want to hear) so that changes, if any, can be detected when the survey is retaken.     4    Teacher constantly acts as effective facilitator and coach.    9    16
3    9210    Choose the response that best reflects your actual teaching practice (not what you think would be best or that we want to hear) so that changes, if any, can be detected when the survey is retaken.     1    Teacher plans only whole-class instruction.    0    1
3    9210    Choose the response that best reflects your actual teaching practice (not what you think would be best or that we want to hear) so that changes, if any, can be detected when the survey is retaken.     2    Teacher plans whole-class instruction but occasionally uses small-group instruction.    2    1
3    9210    Choose the response that best reflects your actual teaching practice (not what you think would be best or that we want to hear) so that changes, if any, can be detected when the survey is retaken.     3    Teacher plans whole-class instruction and regularly uses cooperative learning groups.    14    5
3    9210    Choose the response that best reflects your actual teaching practice (not what you think would be best or that we want to hear) so that changes, if any, can be detected when the survey is retaken.     4    Teacher effectively plans for whole-class instruction as needed and frequently uses cooperative learning groups.    7    16