在 SQL 中，如何计算列中值的数量，然后对其进行透视以使该列成为行？

Question

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

Id    Quality?  Speed?
--    -------   -----
1     3         1
2     2         1
3     2         3
4     3         2

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

Question  1    2    3    Total
--------  --   --   --   -----
Quality?  0    2    2    10
Speed?    2    1    1    7

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

Answer 1

这将为您提供您所要求的内容：

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

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

Answer 2

您还可以利用 SQL 2005 的旋转功能来实现您想要的目的。这样您就不需要像在交叉表中那样对任何问题进行硬编码。请注意，我将源表称为“mytable”，并使用公用表表达式来提高可读性，但您也可以使用子查询。

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

请注意，如果您不想进行细分，则查询会更简单：

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