Home     |     .Net Programming    |     cSharp Home    |     Sql Server Home    |     Javascript / Client Side Development     |     Ajax Programming

Ruby on Rails Development     |     Perl Programming     |     C Programming Language     |     C++ Programming     |     IT Jobs

Python Programming Language     |     Laptop Suggestions?    |     TCL Scripting     |     Fortran Programming     |     Scheme Programming Language


 
 
Cervo Technologies
The Right Source to Outsource

MS Dynamics CRM 3.0

Sql Server Programming

Newbie Needs Help - Pivot Query? CASE Statement?


Using SQL 2000. Database is based on 14 test questions. Each question is a
separate column that can be answered yes or no. I need  direction in creating
a query that would produce results like:

                        Yes    No
Question 1          11      5
Question 2            2      7
Question 3            5     12

Any help is greatly appreciated!!

--
Message posted via SQLMonster.com
http://www.sqlmonster.com/Uwe/Forums.aspx/sql-server-programming/2007...

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

> Using SQL 2000. Database is based on 14 test questions. Each question is a
> separate column that can be answered yes or no. I need  direction in
> creating
> a query that would produce results like:

>                        Yes    No
> Question 1          11      5
> Question 2            2      7
> Question 3            5     12

> Any help is greatly appreciated!!

I have a few questions about the specifics for this problem, but first I
have to ask about "Each question is a separate column..." Is this really
your current design?  If so, you might consider normalizing it.

-----------------------------------------------Reply-----------------------------------------------
See if the following works for you:

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...

Add to del.icio.us | Digg this | Stumble it | Powered by Megasolutions Inc