In SQL, how can I count the number of values in a column and then pivot it so the column becomes the
- by Josh Yeager
I have a survey database with one column for each question and one row for each person who responds. Each question is answered with a value from 1 to 3.
Id Quality? Speed?
-- ------- -----
1 3 1
2 2 1
3 2 3
4 3 2
Now, I need to display the results as one row per question, with a column for each response number, and the value in each column being the number of responses that used that answer. Finally, I need to calculate the total score, which is the number of 1's plus two times the number of 2's plus three times the number of threes.
Question 1 2 3 Total
-------- -- -- -- -----
Quality? 0 2 2 10
Speed? 2 1 1 7
Is there a way to do this in set-based SQL? I know how to do it using loops in C# or cursors in SQL, but I'm trying to make it work in a reporting tool that doesn't support cursors.