Test Results - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

Test Results

Author

Topic

rdprecure
Starting Member

7 Posts

Posted - 2013-01-11 : 02:26:39

I am trying to generate a report showing which questions were missed on an online test for a given student. Can someone help me with this?

The report should look something like:

Megan missed the following questions:
1. Capitol of California
4. Capitol of Idaho
5. Capitol of Nevada

Here are the tables...

CREATE TABLE Tests(
ID [bigint] IDENTITY(1,1) NOT NULL,
UserID [varchar](10) NOT NULL,
TQ1 [int] NULL,
TA1 [int] NULL,
TQ2 [int] NULL,
TA2 [int] NULL,
TQ3 [int] NULL,
TA3 [int] NULL,
TQ4 [int] NULL,
TA4 [int] NULL,
TQ5 [int] NULL,
TA5 [int] NULL)

INSERT INTO Tests (UserID,TQ1,TA1,TQ2,TA2,TQ3,TA3,TQ4,TA4,TQ5,TA5)
VALUES ('John',6,2,8,3,2,2,10,1,5,4)
INSERT INTO Tests (UserID,TQ1,TA1,TQ2,TA2,TQ3,TA3,TQ4,TA4,TQ5,TA5)
VALUES ('Megan',3,1,8,3,7,4,9,2,6,3)
INSERT INTO Tests (UserID,TQ1,TA1,TQ2,TA2,TQ3,TA3,TQ4,TA4,TQ5,TA5)
VALUES ('David',2,2,3,2,10,2,5,3,6,1)
INSERT INTO Tests (UserID,TQ1,TA1,TQ2,TA2,TQ3,TA3,TQ4,TA4,TQ5,TA5)
VALUES ('Suzan',10,4,4,3,1,5,8,2,9,1)
INSERT INTO Tests (UserID,TQ1,TA1,TQ2,TA2,TQ3,TA3,TQ4,TA4,TQ5,TA5)
VALUES ('Robert',1,3,6,1,2,3,4,1,5,4)
INSERT INTO Tests (UserID,TQ1,TA1,TQ2,TA2,TQ3,TA3,TQ4,TA4,TQ5,TA5)
VALUES ('Keith',3,3,2,2,10,1,7,4,8,2)

CREATE TABLE QuestionList(
ID [int] NULL,
Question [varchar](50) NOT NULL,
Answer1 [varchar](50) NULL,
IsCorrect1 [bit] NULL,
Answer2 [varchar](50) NULL,
IsCorrect2 [bit] NULL,
Answer3 [varchar](50) NULL,
IsCorrect3 [bit] NULL,
Answer4 [varchar](50) NULL,
IsCorrect4 [bit] NULL,
Answer5 [varchar](50) NULL,
IsCorrect5 [bit] NULL)

INSERT INTO QuestionList (ID,Question,Answer1,IsCorrect1,Answer2,IsCorrect2,Answer3,IsCorrect3,Answer4,IsCorrect4,Answer5,IsCorrect5)
VALUES(1,'Capital of Texas?','Houston',0,'San Antonio',0,'Austin',1,'Amarillo',0,'Dallas',0)
INSERT INTO QuestionList (ID,Question,Answer1,IsCorrect1,Answer2,IsCorrect2,Answer3,IsCorrect3,Answer4,IsCorrect4)
VALUES(2,'Capital of Florida?','Tallahassee',1,'Miami',0,'Jacksonville',0,'Tampa',0)
INSERT INTO QuestionList (ID,Question,Answer1,IsCorrect1,Answer2,IsCorrect2,Answer3,IsCorrect3,Answer4,IsCorrect4)
VALUES(3,'Capital of California?','Bakersfield',0,'San Diego',0,'Los Angeles',0,'Sacramento',1)
INSERT INTO QuestionList (ID,Question,Answer1,IsCorrect1,Answer2,IsCorrect2,Answer3,IsCorrect3,Answer4,IsCorrect4)
VALUES(4,'Capital of New York?','New York',0,'Albany',1,'Rochester',0,'Syracuse',0)
INSERT INTO QuestionList (ID,Question,Answer1,IsCorrect1,Answer2,IsCorrect2,Answer3,IsCorrect3,Answer4,IsCorrect4)
VALUES(5,'Capital of Oklahoma?','Norman',0,'Oklahoma City',1,'Tulsa',0,'Duncanville',0)
INSERT INTO QuestionList (ID,Question,Answer1,IsCorrect1,Answer2,IsCorrect2,Answer3,IsCorrect3)
VALUES(6,'Capital of Nevada?','Carson City',1,'Las Vegas',0,'Reno',0)
INSERT INTO QuestionList (ID,Question,Answer1,IsCorrect1,Answer2,IsCorrect2,Answer3,IsCorrect3,Answer4,IsCorrect4)
VALUES(7,'Capital of Ohio?','Cleveland',0,'Toledo',0,'Dayton',0,'Columbus',1)
INSERT INTO QuestionList (ID,Question,Answer1,IsCorrect1,Answer2,IsCorrect2,Answer3,IsCorrect3,Answer4,IsCorrect4)
VALUES(8,'Capital of Georgia?','Augusta',0,'Columbus',0,'Atlanta',1,'Athens',0)
INSERT INTO QuestionList (ID,Question,Answer1,IsCorrect1,Answer2,IsCorrect2,Answer3,IsCorrect3,Answer4,IsCorrect4)
VALUES(9,'Capital of Idaho?','Boise',1,'Caldwell',0,'Dubois',0,'Idaho City',0)
INSERT INTO QuestionList (ID,Question,Answer1,IsCorrect1,Answer2,IsCorrect2,Answer3,IsCorrect3,Answer4,IsCorrect4)
VALUES(10,'Capital of Michigan?','Detroit',0,'Lansing',1,'Grand Rapids',0,'Dearborn',0)

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2013-01-11 : 03:06:42

have a look at UNPIVOT operator. thats what you need to do and then join them on questionid

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2013-01-11 : 03:08:20

also if possible try to redesign table to store them as rows if you want to perform comparisons like this

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources