Creating multiple data groups in SQLServer

Question

I scratched my head over this, but I feel that I do not notice the obvious answer, and none of my search phrases led to any results. I am compiling the survey results in a database in the following structure.

ID  TestName     Q1    Q2    Q3    Q4
--  ---------  ----  ----  ----  ----
1   test1        1     2     1     1
2   test1        2     3     4     1
3   test2        1     1     4     2
4   test1        2     5     3     4
5   test2        1     5     2     4

I want to group similar results in each column only if they have the same name and get the amount from each similar result. My end result is shown below, but I'm trying to figure out how to write a query without having 4 separate queries.

Test 1
        Question 1 Results
        1(1)
        2(2)
        3(0)
        4(0)
        5(0)

        Question 2 Results
        1(0)
        2(1)
        3(1)
        4(0)
        5(1)

        Question 3 Results
        1(1)
        2(0)
        3(1)
        4(1)
        5(0)

        Question 4 Results
        1(2)
        2(0)
        3(0)
        4(1)
        5(0)

Test 2
        Question 1 Results
        1(2)
        2(0)
        3(0)
        4(0)
        5(0)

        Question 2 Results
        1(1)
        2(0)
        3(0)
        4(0)
        5(1)

        Question 3 Results
        1(0)
        2(1)
        3(0)
        4(1)
        5(0)

        Question 4 Results
        1(0)
        2(1)
        3(0)
        4(1)
        5(0)

Answer 1

declare @YourTable table (
    id int,
    TestName varchar(10),
    q1 char(1),
    q2 char(1),
    q3 char(1),
    q4 char(1)
)

insert into @YourTable
    (id, TestName, q1, q2, q3, q4)
    select 1,'test1','1','2','1','1' union all
    select 2,'test1','2','3','4','1' union all
    select 3,'test2','1','1','4','2' union all
    select 4,'test1','2','5','3','4' union all
    select 5,'test2','1','5','2','4'

select TestName, 
       sum(case when q1 = '1' then 1 else 0 end) as [q1(1)],
       sum(case when q1 = '2' then 1 else 0 end) as [q1(2)],
       /* ... */
       sum(case when q4 = '4' then 1 else 0 end) as [q4(4)],      
       sum(case when q4 = '5' then 1 else 0 end) as [q4(5)]
    from @YourTable
    group by TestName

Answer 2

sort of

select name, sum(q1), sum(q2), sum(q3), sum(q4)
from tbl
group by name

if you store q values ​​as varchar, you can convert them to numeric ones before adding them up:

sum(cast(q1 as int))