Convert rows to columns in postgresql with joins

Question

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

score 1 · Answer 1 · answered Jul 24 '15 at 16:56

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

answered Jul 24 '15 at 16:56

Patrick Armentrout

11
2

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. – bungee41 Jul 27 '15 at 13:28

Convert rows to columns in postgresql with joins

1 Answers1