0

all. I have a very similar question to this one. I'm trying to convert rows to columns within results, but I also need to join a few tables together. I'm starting to grasp how to do this within one table, but I have no idea how to use joins to accomplish the same thing.

I have 3 tables - test, question, and answer. A test has questions. Questions have answers. Pretty obvious so far. I need to pull a list of questions and answers for a certain test, but I'd like it in a certain format. (I didn't set these up - this is what I have to work with.)

Like I said, this is very similar to another post on moving rows to columns, but I wasn't sure how to incorporate the joins with it.

Here's the table structure:

TEST
----------
id
task_id
name

QUESTION
----------
id
test_id
text
correctanswer_id

ANSWER
----------
id
question_id
text

Here's what I have now and what I get from it:

-- select all review questions for a specific video
select t.id as "testid", q.id as "questionid", q.text as "questiontext",
    a.id as "answerid", a.text as "answertext"
from "Question" q
    inner join "Answer" a on a.question_id = q.id
    inner join "Test" t on t.id = q.test_id
where t.test_id = 100

+--------+------------+--------------+----------+------------+
| testid | questionid | questiontext | answerid | answertext |
+--------+------------+--------------+----------+------------+
| 100    | 200        | 'Question 1' | 300      | 'Answer 1' |
| 100    | 200        | 'Question 1' | 301      | 'Answer 2' |
| 100    | 200        | 'Question 1' | 302      | 'Answer 3' |
| 100    | 200        | 'Question 1' | 303      | 'Answer 4' |
| 100    | 201        | 'Question 2' | 304      | 'Answer 1' |
| 100    | 201        | 'Question 2' | 305      | 'Answer 2' |
| 100    | 201        | 'Question 2' | 306      | 'Answer 3' |
| 100    | 201        | 'Question 2' | 307      | 'Answer 4' |
+--------+------------+--------------+----------+------------+

Here's what I need instead:

+--------+------------+--------------+-----------+-------------+-----------+-------------+-----------+-------------+-----------+-------------+------------------+
| testid | questionid | questiontext | answerid1 | answertext1 | answerid2 | answertext2 | answerid3 | answertext3 | answerid4 | answertext4 | correctanswer_id |
+--------+------------+--------------+-----------+-------------+-----------+-------------+-----------+-------------+-----------+-------------+------------------+
| 100    | 200        | 'Question 1' | 300       | 'Answer 1'  | 301       | 'Answer 2'  | 302       | 'Answer 3'  | 303       | 'Answer 4'  | 301              |
| 100    | 201        | 'Question 2' | 304       | 'Answer 1'  | 305       | 'Answer 2'  | 306       | 'Answer 3'  | 307       | 'Answer 4'  | 307              |
+--------+------------+--------------+-----------+-------------+-----------+-------------+-----------+-------------+-----------+-------------+------------------+

1 Answer 1

1

Have you tried using a Case Statement in your query?

select t.id as "testid", q.id as "questionid", q.text as "questiontext",
    a.id as "answerid", a.text as "answertext", CASE WHEN answertext= 'Answer 1' then answertext as answertext1
from "Question" q
    inner join "Answer" a on a.question_id = q.id
    inner join "Test" t on t.id = q.test_id
where t.test_id = 100

Here is a link for more detail: http://www.postgresql.org/docs/7.4/static/functions-conditional.html

Sign up to request clarification or add additional context in comments.

1 Comment

That wouldn't work because this is a series of test questions and every question has unique answer responses. It has to be completely variable.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.