В SQL, как я могу считать количество значений в столбце и затем вертеться оно так, столбец становится строкой?
У меня есть база данных обзора с одним столбцом для каждого вопроса и одной строкой для каждого человека, который отвечает. На каждый вопрос отвечают со значением от 1 до 3.
Id Quality? Speed?
-- ------- -----
1 3 1
2 2 1
3 2 3
4 3 2
Теперь, я должен отобразить результаты как одну строку на вопрос со столбцом для каждого числа ответа и значением в каждом столбце, являющемся количеством ответов, которые использовали тот ответ. Наконец, я должен вычислить общий счет, который является количеством 1's плюс два раза количество 2's плюс три раза количество троек.
Question 1 2 3 Total
-------- -- -- -- -----
Quality? 0 2 2 10
Speed? 2 1 1 7
Существует ли способ сделать это в основанном на наборе SQL? Я знаю, как сделать это с помощью циклов в C# или курсоров в SQL, но я пытаюсь заставить его работать в инструменте создания отчетов, который не поддерживает курсоры.
2 ответа
Это даст вам то, о чем вы просите:
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, вместо того, чтобы помещать их в одну таблицу в виде столбцов. Это начинает немного напоминать конструкцию пары сущность-значение, но я думаю, что она достаточно отличается, чтобы быть полезной здесь.
Вы также можете использовать функции поворота 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