create table tests
(
  testid varchar(10) not null,
  studentid varchar(10) not null,
  q01 varchar(3) not null check(q01 in('yes', 'no')),
  q02 varchar(3) not null check(q02 in('yes', 'no')),
  q03 varchar(3) not null check(q03 in('yes', 'no')),
  q04 varchar(3) not null check(q04 in('yes', 'no')),
  q05 varchar(3) not null check(q05 in('yes', 'no')),
  q06 varchar(3) not null check(q06 in('yes', 'no')),
  q07 varchar(3) not null check(q07 in('yes', 'no')),
  q08 varchar(3) not null check(q08 in('yes', 'no')),
  q09 varchar(3) not null check(q09 in('yes', 'no')),
  q10 varchar(3) not null check(q10 in('yes', 'no')),
  q11 varchar(3) not null check(q11 in('yes', 'no')),
  q12 varchar(3) not null check(q12 in('yes', 'no')),
  q13 varchar(3) not null check(q13 in('yes', 'no')),
  q14 varchar(3) not null check(q14 in('yes', 'no')),
  primary key(testid, studentid)
);

insert into tests values('test1', 'student1', 'yes', 'no', 'yes', 'no',
'yes', 'no', 'yes', 'no', 'yes', 'no', 'yes', 'no', 'yes', 'no');
insert into tests values('test1', 'student2', 'no', 'yes', 'no', 'yes',
'no', 'yes', 'no', 'yes', 'no', 'yes', 'no', 'yes', 'no', 'yes');
insert into tests values('test1', 'student3', 'no', 'yes', 'yes', 'no',
'yes', 'yes', 'no', 'yes', 'yes', 'no', 'yes', 'yes', 'no', 'yes');

select testid, question,
  sum(
    case
      when question =  1 and q01 = 'yes' then 1
      when question =  2 and q02 = 'yes' then 1
      when question =  3 and q03 = 'yes' then 1
      when question =  4 and q04 = 'yes' then 1
      when question =  5 and q05 = 'yes' then 1
      when question =  6 and q06 = 'yes' then 1
      when question =  7 and q07 = 'yes' then 1
      when question =  8 and q08 = 'yes' then 1
      when question =  9 and q09 = 'yes' then 1
      when question = 10 and q10 = 'yes' then 1
      when question = 11 and q11 = 'yes' then 1
      when question = 12 and q12 = 'yes' then 1
      when question = 13 and q13 = 'yes' then 1
      when question = 14 and q14 = 'yes' then 1
    end) as [yes],
  sum(
    case
      when question =  1 and q01 = 'no' then 1
      when question =  2 and q02 = 'no' then 1
      when question =  3 and q03 = 'no' then 1
      when question =  4 and q04 = 'no' then 1
      when question =  5 and q05 = 'no' then 1
      when question =  6 and q06 = 'no' then 1
      when question =  7 and q07 = 'no' then 1
      when question =  8 and q08 = 'no' then 1
      when question =  9 and q09 = 'no' then 1
      when question = 10 and q10 = 'no' then 1
      when question = 11 and q11 = 'no' then 1
      when question = 12 and q12 = 'no' then 1
      when question = 13 and q13 = 'no' then 1
      when question = 14 and q14 = 'no' then 1
    end) as [no]
from tests,
  (          select  1 as question
   union all select  2
   union all select  3
   union all select  4
   union all select  5
   union all select  6
   union all select  7
   union all select  8
   union all select  9
   union all select 10
   union all select 11
   union all select 12
   union all select 13
   union all select 14) as questions
group by testid, question;

Output:

testid     question    yes         no
---------- ----------- ----------- -----------
test1      1           1           2
test1      2           2           1
test1      3           2           1
test1      4           1           2
test1      5           2           1
test1      6           2           1
test1      7           1           2
test1      8           2           1
test1      9           2           1
test1      10          1           2
test1      11          2           1
test1      12          2           1
test1      13          1           2
test1      14          2           1

--
BG, SQL Server MVP
www.SolidQualityLearning.com
www.insidetsql.com

"Chamark via SQLMonster.com" <u21870@uwe> wrote in message
news:72ed32b5d655f@uwe...