How can I seperate one column into multiple columns depending on their value when selecting it?

Question

I have a table called assignment_answers, which has the following attributes: assignment_answers_id, question_id and order. The order is an attribute, which can take a value from 0 to 9. I would like for every value that it can take to make it be displayed in a different column. For instance when the order has value 0, then I want it to be displayed in a column called number0. When it has value 1 I want it to be displayed in a column called number1.

Could someone help me with that? So far I have tried this but it does not work:

SELECT (CASE WHEN assessment_answers.order = 0 
             THEN(
              select aq.order as number0
              from assessment_answers)
              END)
       (CASE WHEN assessment_answers.order = 1 
             THEN(
              select aq.order as number1
              from assessment_answers)
              END)
FROM assessment_answers

I get an error saying: ERROR: syntax error at or near "(" LINE 6: (CASE WHEN assessment_questions."order" = 1

SAMPLE DATA

assignment_answers_id     question_id    order
 1                            1           0
 2                            1           0
 3                            2           1

desired output:

assignment_answers_id     question_id    order0  order1
 1                            1           0       null
 2                            1           0       null
 3                            2           null     1

Answer 1

You can try to use normal CASE WHEN

Query 1:

SELECT assignment_answers_id,
       question_id,
       (CASE WHEN order = 0 THEN order END) order0,
       (CASE WHEN order = 1 THEN order END) order1
FROM assessment_answers

Results:

| assignment_answers_id | question_id | order0 | order1 |
|-----------------------|-------------|--------|--------|
|                     1 |           1 |      0 | (null) |
|                     2 |           1 |      0 | (null) |
|                     3 |           2 | (null) |      1 |