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.
SELECT Id, Area, Result, Pass, Fail FROM -- Error as Id and Result columns are used in pivot. (SELECT Id, Area, Result FROM dbo. Result WHERE Area = 'A') as P. PIVOT ( count(Id) FOR Result in (Pass, Fail) ) AS RESULT.
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.
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
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With