选择时，如何根据列的值将一列分成多列?

Question

我有一个名为assignment_answers的表，它具有以下属性：assignment_answers_id，question_id和命令。顺序是一个属性，可以采用 0 到 9 之间的值。 我希望每个值都可以使其显示在不同的列中。例如，当订单的值为 0 时，我希望它显示在名为number0的列中。当它的值为 1 时，我希望它显示在名为number1的列中。

有人可以帮助我吗？到目前为止，我已经尝试过这个，但它不起作用：

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

我收到一个错误，说： 错误：在"("处或附近出现语法错误 第 6 行：(assessment_questions时的情况。订单" = 1

示例数据

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

期望的输出：

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

Answer 1

您可以尝试使用普通CASE WHEN

查询 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

结果：

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

Answer 2

这能做到你想要的吗？

select (aa.order = 0)::int as order_0, 
(aa.order = 1)::int as order_1, 
(aa.order = 2)::int as order_2, 
. . .
from  assessment_answers aa;