在 SQL 中，如何计算列中值的数量，然后将其旋转以使该列成为行? [英] In SQL, how can I count the number of values in a column and then pivot it so the column becomes the row?

问题描述

我有一个调查数据库，每个问题占一列，每个回答者占一行.每个问题都用一个从 1 到 3 的值来回答.

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

现在，我需要将结果显示为每个问题一行，每个响应编号有一列，每列中的值是使用该答案的响应数量.最后，我需要计算总分，也就是1的个数加上2的个数的2倍加上3的个数的3倍.

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

有没有办法在基于集合的 SQL 中做到这一点?我知道如何使用 C# 中的循环或 SQL 中的游标来执行此操作，但我正在尝试使其在不支持游标的报告工具中工作.

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
    '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

请记住，当您添加问题甚至可能的答案时，这会迅速膨胀.如果您稍微规范化并有一个 Answers 表，其中每个答案都有一行并带有问题代码或 id，而不是将它们作为列放在一个表中，那么您的情况可能会好得多.它开始看起来有点像实体值对设计，但我认为它的不同之处足以在这里有用.

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.

这篇关于在 SQL 中，如何计算列中值的数量，然后将其旋转以使该列成为行?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！