时间戳

首页 > 解决方案 > 合并表postgresql中相同的行

sql - 合并表postgresql中相同的行

问题描述

我在 postgresql 中有下表：

id | grade | comment
--------------------
01 | A     | Good
01 | B     | OK
01 | C     | BAD
02 | A     | Good
02 | E     | Very BAD
02 | D     | BAD

我想获取以下格式的记录

id | grade1 | grade2 | grade3 | comment1 | comment2 | comment3  
--------------------------------------------------------------
01 | A      | B      | C      | Good     | OK       | BAD
02 | A      | E      | D      | Good     | Very BAD | BAD

任何人都可以帮忙吗？

标签： sqlpostgresqlgroup-bypivot

解决方案

您可以使用row_number()条件聚合：

select 
    id,
    max(grade)   filter(where rn = 1) grade1,
    max(grade)   filter(where rn = 2) grade2,
    max(grade)   filter(where rn = 3) grade3,
    max(comment) filter(where rn = 1) comment1,
    max(comment) filter(where rn = 2) comment2,
    max(comment) filter(where rn = 3) comment3
from (
    select t.*, row_number() over(partition by id order by grade) rn
    from mytable t
) t
group by id

推荐阅读