在 Postgres 中如何将行转换为列? [英] How do you convert rows to columns in Postgres?

问题描述

我有一个包含以下列的表格:user_id、grade.

I have a table with the following columns: user_id, grade.

user_id | grade
1         k
1         3
2         4
2         5
3         2

我希望能够制作一个看起来像 user_id、grade_k、grade_1 等的表格，带有 true 或 false如果用户教授该年级，则为每个年级.

I would like to be able to make a table that looks like user_id, grade_k, grade_1, etc, with a true or false for each grade if the user teaches that grade.

似乎您可以使用 crosstab 来执行此操作，但有人可以给我举个例子吗?

It seems like you could use crosstab to do this, but could someone show me an example?

所需的输出:

user_id | grade_k | grade_1 | grade_2 | grade_3 | grade_4 | grade_5
1         1         0         0         1         0         0
2         0         0         0         0         1         1
3         0         0         1         0         0         0

我想这样做的原因是这种格式可以更容易地在数据可视化工具中进行过滤.

The reason I want to do this is that this format will make it easier to do filtering in a data visualization tool.

推荐答案

另一个选项，类似于过滤聚合，将使用常规数组聚合并检查是否存在，如

Another option, similar to a filtered aggregation, would be to use a regular array aggregation and check for existence, like

WITH users (user_id, grade) AS (
    VALUES
        (1, 'k'),
        (1, '1'),
        (2, '4'),
        (2, '5'),
        (3, '2')
),
user_grades AS (
    SELECT user_id, array_agg(grade) AS grades
    FROM users
    GROUP BY user_id
)
SELECT
    user_id,
    'k' = ANY(grades) AS teaches_k,
    '1' = ANY(grades) AS teaches_1,
    '2' = ANY(grades) AS teaches_2,
    '3' = ANY(grades) AS teaches_3,
    '4' = ANY(grades) AS teaches_4,
    '5' = ANY(grades) AS teaches_5
FROM
    user_grades

这篇关于在 Postgres 中如何将行转换为列?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！