这将给您您所要的:
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的答案做一个答案表,而不是将它们作为一列放在一个表中,则可能会更好。它开始看起来有点像实体- 值对设计,但我认为它的区别足以在此处使用。