January 11, 2013 at 12:38 am
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:
[font="Arial"]Megan missed the following questions:
1. Capitol of California
4. Capitol of Idaho
5. Capitol of Nevada[/font]
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)
January 11, 2013 at 6:00 am
Looks like homework, which is fine, I am happy to help, but please show us what have you tried so far?
PS Thank you for providing consumable DDL and DML so we can setup a test environment on our side. You would be surprised how many people do not bother when asking for help.
There are no special teachers of virtue, because virtue is taught by the whole community.
--Plato
January 12, 2013 at 12:18 am
Not homework, actual website. I greatly simplified the sample tables for clarity. The website (thspa.us) provides online testing of the judges used at powerlifting meets. I am able to generate the judges tests and score them, but I need to provide a list of the questions they missed. The test is 50 questions long and can take as long as an hour to complete, and simply telling them how many questions they missed is causing some testing anxiety. I've tried several ways, including reformatting the data into a temporary table. I'm working now on using PIVOT and UNPIVOT (just found out about those yesterday). I'll see how far I get.
January 12, 2013 at 5:30 am
This is unpivoting multiple columns. See how these results look:
WITH TestQuestions(Id, Question, AnswerNumber, Answer, IsCorrect)
AS (
SELECT Id,
Question,
ROW_NUMBER() OVER (PARTITION BY Id ORDER BY Answers) AS AnswerNumber,
Answer,
IsCorrect
FROM (
SELECT ID,
Question,
Answer1,
IsCorrect1,
Answer2,
IsCorrect2,
Answer3,
IsCorrect3,
Answer4,
IsCorrect4,
Answer5,
IsCorrect5
FROM dbo.QuestionList
) Main UNPIVOT
( Answer FOR Answers IN (Answer1, Answer2, Answer3, Answer4, Answer5) ) Sup UNPIVOT
( IsCorrect FOR IsCorrects IN (IsCorrect1, IsCorrect2, IsCorrect3, IsCorrect4, IsCorrect5) ) Ct
WHERE RIGHT(Answers, 1) = RIGHT(IsCorrects, 1)
),
TestAnswers(Id, UserID, QuestionNumber, Question, Answer)
AS (
SELECT Id,
UserID,
ROW_NUMBER() OVER (PARTITION BY Id ORDER BY Questions) AS QuestionNumber,
Question,
Answer
FROM (
SELECT ID,
UserID,
TQ1,
TQ2,
TQ3,
TQ4,
TQ5,
TA1,
TA2,
TA3,
TA4,
TA5
FROM dbo.Tests
--WHERE UserID = 'David'
) Main UNPIVOT
( Question FOR Questions IN (TQ1, TQ2, TQ3, TQ4, TQ5) ) Sup UNPIVOT
( Answer FOR Answers IN (TA1, TA2, TA3, TA4, TA5) ) Ct
WHERE RIGHT(Questions, 1) = RIGHT(Answers, 1)
)
SELECT DISTINCT
*
FROM TestQuestions tq
JOIN TestAnswers ta ON tq.Id = ta.Question
AND tq.AnswerNumber = ta.Answer
WHERE IsCorrect = 0
ORDER BY UserID,
QuestionNumber;
There are no special teachers of virtue, because virtue is taught by the whole community.
--Plato
January 12, 2013 at 12:23 pm
Holy crap that's good stuff! Works perfectly. I didn't expect anyone to write the whole thing. Thank you very much. Don't know how long it took you to write it but it will take me a while to break it down and figure out how it works. Several lessons in one SQL statement...
January 12, 2013 at 4:56 pm
Not a problem. I hadn't played with unpivoting data in a long time so I took it on as a learning challenge. It took me about a half hour to produce the code.
Here, however, is a much improved version that does not use UNPIVOT at all that should be easier to understand, and more importantly should scale up much better performance-wise.
IF OBJECT_ID(N'tempdb..#TestQuestionsAndAnswers') IS NOT NULL
DROP TABLE #TestQuestionsAndAnswers;
IF OBJECT_ID(N'tempdb..#TestResults') IS NOT NULL
DROP TABLE #TestResults;
SELECT q.Id AS QuestionID,
q.Question,
d.Answer,
d.IsCorrect,
d.AnswerID
INTO #TestQuestionsAndAnswers
FROM dbo.QuestionList q
CROSS APPLY (
SELECT q.Answer1,
q.IsCorrect1,
1 AS AnswerID
UNION ALL
SELECT q.Answer2,
q.IsCorrect2,
2 AS AnswerID
UNION ALL
SELECT q.Answer3,
q.IsCorrect3,
3 AS AnswerID
UNION ALL
SELECT q.Answer4,
q.IsCorrect4,
4 AS AnswerID
UNION ALL
SELECT q.Answer5,
q.IsCorrect5,
5 AS AnswerID
) d (Answer, IsCorrect, AnswerID)
WHERE d.Answer IS NOT NULL;
SELECT t.ID AS UserUniqueID,
t.UserID,
d.TQ AS QuestionID,
d.TA AS Answer
INTO #TestResults
FROM dbo.Tests t
CROSS APPLY (
SELECT t.TQ1,
t.TA1
UNION ALL
SELECT t.TQ2,
t.TA2
UNION ALL
SELECT t.TQ3,
t.TA3
UNION ALL
SELECT t.TQ4,
t.TA4
UNION ALL
SELECT t.TQ5,
t.TA5
) d (TQ, TA);
SELECT *
FROM #TestQuestionsAndAnswers;
SELECT *
FROM #TestResults;
GO
SELECT *
FROM #TestQuestionsAndAnswers qa
JOIN #TestResults tr ON qa.QuestionID = tr.QuestionID
AND qa.AnswerID = tr.Answer
ORDER BY tr.UserID,
qa.QuestionID;
edit: code formatting
There are no special teachers of virtue, because virtue is taught by the whole community.
--Plato
January 13, 2013 at 8:08 pm
Two for the price of one...my day just keeps getting better. Thanks a bunch for the code. I do like this one better. Definitely easier to understand. While I was having a little trouble scaling the first one up for a 50 question test (plus a few other tweaks that weren't reflected in the sample tables I gave you) this went much smoother. Thanks again and have a good week...mine's starting out great.
January 13, 2013 at 8:54 pm
You're quite welcome. I am happy it helped.
There are no special teachers of virtue, because virtue is taught by the whole community.
--Plato
January 16, 2013 at 5:53 pm
opc.three (1/12/2013)
Not a problem. I hadn't played with unpivoting data in a long time so I took it on as a learning challenge. It took me about a half hour to produce the code.Here, however, is a much improved version that does not use UNPIVOT at all that should be easier to understand, and more importantly should scale up much better performance-wise.
IF OBJECT_ID(N'tempdb..#TestQuestionsAndAnswers') IS NOT NULLDROP TABLE #TestQuestionsAndAnswers;
IF OBJECT_ID(N'tempdb..#TestResults') IS NOT NULL
DROP TABLE #TestResults;
SELECT q.Id AS QuestionID,
q.Question,
d.Answer,
d.IsCorrect,
d.AnswerID
INTO #TestQuestionsAndAnswers
FROM dbo.QuestionList q
CROSS APPLY (
SELECT q.Answer1,
q.IsCorrect1,
1 AS AnswerID
UNION ALL
SELECT q.Answer2,
q.IsCorrect2,
2 AS AnswerID
UNION ALL
SELECT q.Answer3,
q.IsCorrect3,
3 AS AnswerID
UNION ALL
SELECT q.Answer4,
q.IsCorrect4,
4 AS AnswerID
UNION ALL
SELECT q.Answer5,
q.IsCorrect5,
5 AS AnswerID
) d (Answer, IsCorrect, AnswerID)
WHERE d.Answer IS NOT NULL;
SELECT t.ID AS UserUniqueID,
t.UserID,
d.TQ AS QuestionID,
d.TA AS Answer
INTO #TestResults
FROM dbo.Tests t
CROSS APPLY (
SELECT t.TQ1,
t.TA1
UNION ALL
SELECT t.TQ2,
t.TA2
UNION ALL
SELECT t.TQ3,
t.TA3
UNION ALL
SELECT t.TQ4,
t.TA4
UNION ALL
SELECT t.TQ5,
t.TA5
) d (TQ, TA);
SELECT *
FROM #TestQuestionsAndAnswers;
SELECT *
FROM #TestResults;
GO
SELECT *
FROM #TestQuestionsAndAnswers qa
JOIN #TestResults tr ON qa.QuestionID = tr.QuestionID
AND qa.AnswerID = tr.Answer
ORDER BY tr.UserID,
qa.QuestionID;
edit: code formatting
I still prefer the CROSS APPLY VALUES approach to UNPIVOT (see the first link in my signature). 😀
That is, if you were working in SQL 2008 of course.
My thought question: Have you ever been told that your query runs too fast?
My advice:
INDEXing a poor-performing query is like putting sugar on cat food. Yeah, it probably tastes better but are you sure you want to eat it?
The path of least resistance can be a slippery slope. Take care that fixing your fixes of fixes doesn't snowball and end up costing you more than fixing the root cause would have in the first place.
Need to UNPIVOT? Why not CROSS APPLY VALUES instead?[/url]
Since random numbers are too important to be left to chance, let's generate some![/url]
Learn to understand recursive CTEs by example.[/url]
[url url=http://www.sqlservercentral.com/articles/St
January 17, 2013 at 8:23 am
dwain.c (1/16/2013)
opc.three (1/12/2013)
Not a problem. I hadn't played with unpivoting data in a long time so I took it on as a learning challenge. It took me about a half hour to produce the code.Here, however, is a much improved version that does not use UNPIVOT at all that should be easier to understand, and more importantly should scale up much better performance-wise.
IF OBJECT_ID(N'tempdb..#TestQuestionsAndAnswers') IS NOT NULLDROP TABLE #TestQuestionsAndAnswers;
IF OBJECT_ID(N'tempdb..#TestResults') IS NOT NULL
DROP TABLE #TestResults;
SELECT q.Id AS QuestionID,
q.Question,
d.Answer,
d.IsCorrect,
d.AnswerID
INTO #TestQuestionsAndAnswers
FROM dbo.QuestionList q
CROSS APPLY (
SELECT q.Answer1,
q.IsCorrect1,
1 AS AnswerID
UNION ALL
SELECT q.Answer2,
q.IsCorrect2,
2 AS AnswerID
UNION ALL
SELECT q.Answer3,
q.IsCorrect3,
3 AS AnswerID
UNION ALL
SELECT q.Answer4,
q.IsCorrect4,
4 AS AnswerID
UNION ALL
SELECT q.Answer5,
q.IsCorrect5,
5 AS AnswerID
) d (Answer, IsCorrect, AnswerID)
WHERE d.Answer IS NOT NULL;
SELECT t.ID AS UserUniqueID,
t.UserID,
d.TQ AS QuestionID,
d.TA AS Answer
INTO #TestResults
FROM dbo.Tests t
CROSS APPLY (
SELECT t.TQ1,
t.TA1
UNION ALL
SELECT t.TQ2,
t.TA2
UNION ALL
SELECT t.TQ3,
t.TA3
UNION ALL
SELECT t.TQ4,
t.TA4
UNION ALL
SELECT t.TQ5,
t.TA5
) d (TQ, TA);
SELECT *
FROM #TestQuestionsAndAnswers;
SELECT *
FROM #TestResults;
GO
SELECT *
FROM #TestQuestionsAndAnswers qa
JOIN #TestResults tr ON qa.QuestionID = tr.QuestionID
AND qa.AnswerID = tr.Answer
ORDER BY tr.UserID,
qa.QuestionID;
edit: code formatting
I still prefer the CROSS APPLY VALUES approach to UNPIVOT (see the first link in my signature). 😀
That is, if you were working in SQL 2008 of course.
Agreed. Very nice article. The graphics are excellent towards visualizing what unpivoting really means in terms of reshaping the data.
I think my second code-effort on this post is equivalent to the CROSS APPLY VALUES approach you showed except that the technique is also usable on SQL Server 2005. I will also say that a row constructor reads a little cleaner than a UNION ALL.
There are no special teachers of virtue, because virtue is taught by the whole community.
--Plato
January 17, 2013 at 5:18 pm
opc.three (1/17/2013)
I think my second code-effort on this post is equivalent to the CROSS APPLY VALUES approach you showed except that the technique is also usable on SQL Server 2005. I will also say that a row constructor reads a little cleaner than a UNION ALL.
Yes indeed. Jeff Moden posted that comment into the article's discussion thread. One day I'll have to do a performance comparison. I've found some interesting results with CROSS APPLY VALUES because there are quite a few cases where it parallelizes the query and results in significant elapsed time improvements (also in the discussion thread towards the end I think).
My thought question: Have you ever been told that your query runs too fast?
My advice:
INDEXing a poor-performing query is like putting sugar on cat food. Yeah, it probably tastes better but are you sure you want to eat it?
The path of least resistance can be a slippery slope. Take care that fixing your fixes of fixes doesn't snowball and end up costing you more than fixing the root cause would have in the first place.
Need to UNPIVOT? Why not CROSS APPLY VALUES instead?[/url]
Since random numbers are too important to be left to chance, let's generate some![/url]
Learn to understand recursive CTEs by example.[/url]
[url url=http://www.sqlservercentral.com/articles/St
Viewing 11 posts - 1 through 10 (of 10 total)
You must be logged in to reply to this topic. Login to reply