Creating Multiple Groups of Data in SQLServer

Been scratching my head on this one, but I feel I'm overlooking the obvious answer, and none of my search phrases have lead to any results.. I am compiling the results of a survey in a database, in the following structure.

ID  TestN开发者_开发问答ame     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 the similar results in each column, only if they have they share the same Name, and get a count from each similar result. My final output is shown below, but I am trying to find out how to write the 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)

---

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

---

something like

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

if you're storing the q values as varchar, you can convert them to numeric before you sum them:

sum(cast(q1 as int))