CODEWITHSUNDEEP
sql
user2872841
user2872841

Reputation: 101

Combine multiple inner joined rows into one row with multiple columns

I have a Questions table and an Answers table that has 4 to 8 answers connected to one question. When I want to get a list of questions and their answers, I use the following code:

select q.QuestionID, q.Question, a.Answer
from Question as q inner join Answer as a
on q.QuestionID=a.QuestionID;

This gives me one row for each answer with the question being repeated on each row. However, I want to only get one row per question with the answers in separate columns. If possible, I'd like to also limit this to only 4 answers. If there are more than 4, the rest should just be ignored. This is however not as important.

The four answer columns would be named "Correct", "Wrong1", "Wrong2" and "Wrong3". The first one in the table (with the lowest AnswerID) is the correct one.

Thank you for your help!

Upvotes: 0

Views: 33

Answers (1)

StanislavL
StanislavL

Reputation: 57381

select q.QuestionID, q.Question, 
   CASE <some field>
   WHEN <condition> THEN a.Answer
   END as Correct,
   CASE <some field2>
   WHEN <condition2> THEN a.Answer
   END as Wrong1
   ...
from Question as q inner join Answer as a
on q.QuestionID=a.QuestionID
group by q.QuestionID

You can group by question and define conditioned fields for the "Correct", "Wrong1", "Wrong2" and "Wrong3" columns

Upvotes: 1

Related Questions