In SQL, how can I count the number of values in a column and then pivot it so the column becomes the row?

Question

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.

Answer 1

This will give you what you're asking for:

SELECT
    'quality' AS question,
    SUM(CASE WHEN quality = 1 THEN 1 ELSE 0 END) AS [1],
    SUM(CASE WHEN quality = 2 THEN 1 ELSE 0 END) AS [2],
    SUM(CASE WHEN quality = 3 THEN 1 ELSE 0 END) AS [3],
    SUM(quality)
FROM
    dbo.Answers
UNION ALL
SELECT
    'speed' AS question,
    SUM(CASE WHEN speed = 1 THEN 1 ELSE 0 END) AS [1],
    SUM(CASE WHEN speed = 2 THEN 1 ELSE 0 END) AS [2],
    SUM(CASE WHEN speed = 3 THEN 1 ELSE 0 END) AS [3],
    SUM(speed)
FROM
    dbo.Answers

Keep in mind though that this will quickly balloon as you add questions or even potential answers. You might be much better off if you normalized a bit and had an Answers table with a row for each answer with a question code or id, instead of putting them across as columns in one table. It starts to look a little bit like the entity-value pair design, but I think that it's different enough to be useful here.

Answer 2

You can also leverage SQL 2005's pivoting functions to achieve what you want. This way you don't need to hard code any questions as you do in cross-tabulation. Note that I called the source table "mytable" and I used common table expressions for readability but you could also use subqueries.

WITH unpivoted AS (
    SELECT id, value, question
    FROM mytable a
    UNPIVOT (value FOR question IN (quality,speed) ) p
)
,counts AS (
    SELECT question, value, count(*) AS counts
    FROM unpivoted
    GROUP BY question, value
)
, repivoted AS (
    SELECT question, counts, [1], [2], [3]
    FROM counts
    PIVOT (count(value) FOR value IN ([1],[2],[3])) p 
)
SELECT question, sum(counts*[1]) AS [1], sum(counts*[2]) AS [2], sum(counts*[3]) AS [3]
    ,sum(counts*[1]) + 2*sum(counts*[2]) + 3*sum(counts*[3]) AS Total
FROM repivoted
GROUP BY question

Note if you don't want the breakdown the query is simpler:

WITH unpivoted AS (
    SELECT id, value, question
    FROM mytable a
    UNPIVOT (value FOR question IN (quality,speed) ) p
)
, totals AS (
    SELECT question, value, count(value)*value AS score
    FROM unpivoted
    GROUP BY question, value
)
SELECT question, sum(score) AS score
FROM totals
GROUP BY question