Test results report

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;

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;

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

I still prefer the CROSS APPLY VALUES approach to UNPIVOT (see the first link in my signature). BigGrin

That is, if you were working in SQL 2008 of course.