How can retrieve Answer and Question table in back end with sql query like this?

Question

I'm using Servlet JSP SQL to write Quiz Application

I have two table in sql like this:

Question(questionID, question_content, status, subjectID)

Choice(choiceID, choice_content, questionID, isTrue)

1 Question will have 4 answers (1 true - 3 false)

here is my query:

SELECT *, question_content
FROM Choice 
INNER JOIN Choice.questionID = Question.questionID

So result will be like this:

Question 1 - Choice1

Question 1 - Choice2

Question 1 - Choice3

Question 1 - Choice4

...

So the object I receive in back end will like

Obj(Question1, Choice1)

Obj(Question1, Choice2)

...

But what I want is:

Obj(Question1, Choice1, Choice2, Choice3, Choice4)

Is there any way to achieve this or any query suit my need?

question from:https://stackoverflow.com/questions/65930373/how-can-retrieve-answer-and-question-table-in-back-end-with-sql-query-like-this

Answer 1

Use conditional aggregation as follows:

Select question_content,
       Max(case when rn= 1 then choice_content end) as choice1,
       Max(case when rn= 2 then choice_content end) as choice2,
       Max(case when rn= 3 then choice_content end) as choice3,
       Max(case when rn= 4 then choice_content end) as choice4
 From
(SELECT c.choice_content, q.question_content, q.questionid,
       Row_number() over (partition by q.questionid order by c.choice_id) as rn
FROM Choice c
INNER JOIN question q on c.questionID = q.questionID) t
Group by questionid, question_content