Regarding Combine 2 tables , 2nd table rows into columns

Question

5.00/5 (1 vote)

See more:

SQL-Server-2008R2

Hi Frnds,

I have 2 tables
1) Questions
2) QuestionOptions

Table 1: Questions

SQL

QuestId              Question
   1              select fruit?
   2              select animal?
   3              Select flower?

Table 2 : QuestionOptions

SQL

OptionId          Option     QuestId   CorrectOption
       1              Apple       1              1
       2              Mango       1              1
       3              rose        1              0 
       4              Grapes      2              1  
       5              Lotus       2              0
       6              Orange      3              0
       7              rose        3              1
       8              Cat         3              0

I need output as follows

SQL

QuestId    Option1   Option2     Option3    CorrectOpt1 CorrectOpt2 CorrectOpt3 

   1        Apple     Mango        Rose        1             1         0
   2        Grapes    Lotus        NULL        1             0        NULL
   3        Orange    rose         Cat         0             1         0

First Table Contains Questions and Second Table Contains Answers. I tried using PIVOT concept but not able to get.

Thanks
Suresh

Posted 22-May-14 3:49am

itsureshuk

Add a Solution

2 solutions

Solution 1

SQL

SELECT
    QuestId
    , t1.[Option] AS Option1
    , t2.[Option] AS Option2
    , t3.[Option] AS Option3
    , t1.CorrectOption AS CorrectOpt1
    , t2.CorrectOption AS CorrectOpt2
    , t3.CorrectOption AS CorrectOpt3
FROM
    Questions t
    LEFT OUTER JOIN (
        SELECT
            *, NUM = ROW_NUMBER() OVER(PARTITION BY QuestId ORDER BY [Option])
        FROM
            QuestionOptions t
    ) t1 ON t1.QuestId = t.QuestId AND t1.NUM = 1
    LEFT OUTER JOIN (
        SELECT
            *, NUM = ROW_NUMBER() OVER(PARTITION BY QuestId ORDER BY [Option])
        FROM
            QuestionOptions t
    ) t2 ON t2.QuestId = t.QuestId AND t2.NUM = 2
    LEFT OUTER JOIN (
        SELECT
            *, NUM = ROW_NUMBER() OVER(PARTITION BY QuestId ORDER BY [Option])
        FROM
            QuestionOptions t
    ) t3 ON t3.QuestId = t.QuestId AND t3.NUM = 3

Posted 22-May-14 5:12am

Sergey Vaselenko

Comments

Maciej Los 22-May-14 12:02pm

Nice try ;)
See my answer, please...

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

Maciej Los · Accepted Answer · 2014-05-22T06:02:00

Alternatively to solution1 by Sergey Vaselenko, you can join two pivots:

SQL

DECLARE @Questions TABLE (QID INT IDENTITY(1,1), Question VARCHAR(300))
INSERT INTO @Questions (Question)
VALUES('select fruits:'),
('select animals:'),
('Select flowers:')


DECLARE @QuestionOptions TABLE (QOID INT IDENTITY(1,1), IOption VARCHAR(300), QID INT, CorrectOption INT)

INSERT INTO @QuestionOptions (IOption, QID, CorrectOption)
VALUES('Apple', 1, 1),
('Mango', 1, 1),
('rose', 1, 0 ),
('Grapes', 2, 1  ),
('Lotus', 2, 0),
('Orange', 3, 0),
('rose', 3, 1),
('Cat', 3, 0 )

SELECT T1.QID, T1.Question, T1.[1] AS Answer1, T1.[2] AS Answer2, T1.[3] AS Answer3, T2.[1] AS IsProper1, T2.[2] AS IsProper2, T2.[3] AS IsProper3
FROM (
    SELECT QID, Question, [1], [2], [3]
    FROM (
        SELECT Q.QID, Q.Question, QO.IOption, ROW_NUMBER() OVER(PARTITION BY Q.QID ORDER BY Q.QID) AS OptNo
        FROM @Questions AS Q INNER JOIN @QuestionOptions AS QO ON Q.QID = QO.QID
        ) AS DT
        PIVOT(MAX(IOption) FOR OptNo IN ([1], [2], [3])) AS PT
    ) AS T1 INNER JOIN (
        SELECT Question, [1], [2], [3]
        FROM (
            SELECT Q.QID, Q.Question, QO.CorrectOption, ROW_NUMBER() OVER(PARTITION BY Q.QID ORDER BY Q.QID) AS OptNo
            FROM @Questions AS Q INNER JOIN @QuestionOptions AS QO ON Q.QID = QO.QID
            ) AS ET
            PIVOT(MAX(CorrectOption) FOR OptNo IN ([1], [2], [3])) AS RT
    ) AS T2 ON T1.Question = T2.Question

Result:

1   select fruits:  Apple   Mango   rose    1   1   0
2   select animals: Grapes  Lotus   NULL    1   0   NULL
3   Select flowers: Orange  rose    Cat     0   1   0