Как можно создать таблицу count-ов?

Question

[Абай Керимов]

Здравствуйте. У меня есть таблица Answers, где хранятся оценки на вопросы

Id------QuestionId--------Value
1-------1-------------------5
2-------1-------------------4
3-------2-------------------4
4-------2-------------------3
5-------3-------------------5

Мне надо создать таблицу-статистику, чтобы посчитать для каждого вопроса кол-во пятерок, четверок, троек и тд, то есть таблица должна выглядеть так:

QuestionId------5-----4-----3
1-----------------1-----1-----0
2-----------------0-----1-----1
3-----------------1-----0-----0

Как можно создать MSSQL запрос так, чтобы в итоге создалась такая таблица?

Answer 1

[Сергей Горностаев]

select distinct 
	a.question_id,
	isnull(t5.val, 0) as '5',
	isnull(t4.val, 0) as '4',
	isnull(t3.val, 0) as '3',
	isnull(t2.val, 0) as '2',
	isnull(t1.val, 0) as '1'
from answers as a
left outer join (
	select question_id, count(*) as val from answers where value = 5 group by question_id
) as t5 on a.question_id = t5.question_id
left outer join (
	select question_id, count(*) as val from answers where value = 4 group by question_id
) as t4 on a.question_id = t4.question_id
left outer join (
	select question_id, count(*) as val from answers where value = 3 group by question_id
) as t3 on a.question_id = t3.question_id
left outer join (
	select question_id, count(*) as val from answers where value = 2 group by question_id
) as t2 on a.question_id = t2.question_id
left outer join (
	select question_id, count(*) as val from answers where value = 1 group by question_id
) as t1 on a.question_id = t1.question_id;

Comments

[Абай Керимов]

Спасибо большое, получилось!

Answer 2

[Melkij]

select QuestionId, 
sum(case when Value = 5 then 1 else 0 end) as '5'
sum(case when Value = 4 then 1 else 0 end) as '4'
sum(case when Value = 3 then 1 else 0 end) as '3'
from tablename
group by QuestionId

Возможно ваша СУБД умеет выполнять операцию pivot.