How can I create a table of counts?

Question

SQL

Hello. I have an Answers table that stores scores for questions
Id------QuestionId--------Value
1-------1------------- ------5
2-------1-----4
3-------2----- --------------4
4-------2-----3
5----- --3-------------------5
I need to create a statistics table to calculate the number of fives, fours, triples, etc. for each question, that is, the table should look like this:
QuestionId------5-----4-----3
1-----------------1-----1-- ---0
2-----------------0-----1-----1
3-------------- ---1-----0-----0
How can I create a MSSQL query so that such a table is eventually created?

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;

Answer 2

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

Perhaps your DBMS is able to perform the pivot operation.