PostgreSQL将Group By聚合结果拆分为最多3个单独列

问题背景 #

现有如下SQL语句：

select a.id, string_agg(concat(c.fname), ', ') as approvers 
from
autoapprovalapproverconfig a
left join autoapprovalapproverconfigmandate a2 on a2.autoapprovalaconfigurationid = a.id
left join person p on p.personid = a2.approverid
left join person_contact pc on pc.person_personid = p.personid
left join public.clientcontact c on c.contact_id = pc.personaldetails_contact_id
group by a.id

执行后返回结果：

ID     APPROVERS
--     ---------
1     nameA, nameB
2     nameC
3     nameD, nameE, nameF
4     nameG
5     nameH, nameI

需要修改SQL，去除聚合函数，将每个审批人显示在单独列中，每行最多显示3个审批人，目标格式：

ID     APPROVER1   APPROVER2    APPROVER3
--     ---------   ---------    ---------
1        nameA       nameB
2        nameC
3        nameD       nameE        nameF
4        nameG
5        nameH       nameI

解决方案 #

方法1：条件聚合（无需额外插件） #

利用窗口函数ROW_NUMBER()给每个配置ID下的审批人分配序号，再通过MAX(CASE)提取对应位置的审批人：

select
    a.id,
    max(case when rn = 1 then c.fname end) as approver1,
    max(case when rn = 2 then c.fname end) as approver2,
    max(case when rn = 3 then c.fname end) as approver3
from
    autoapprovalapproverconfig a
left join autoapprovalapproverconfigmandate a2 on a2.autoapprovalaconfigurationid = a.id
left join person p on p.personid = a2.approverid
left join person_contact pc on pc.person_personid = p.personid
left join public.clientcontact c on c.contact_id = pc.personaldetails_contact_id
left join (
    -- 给每个配置下的审批人分配序号
    select
        autoapprovalaconfigurationid,
        approverid,
        row_number() over (partition by autoapprovalaconfigurationid order by approverid) as rn
    from autoapprovalapproverconfigmandate
) a2_rn on a2_rn.autoapprovalaconfigurationid = a.id and a2_rn.approverid = a2.approverid
group by a.id
order by a.id;

方法2：使用crosstab函数（需安装tablefunc扩展） #

如果习惯用交叉表函数，可以先启用tablefunc扩展，再编写查询：

1. 先安装扩展（仅需执行一次）：

CREATE EXTENSION IF NOT EXISTS tablefunc;

2. 编写交叉表查询：

select * from crosstab(
    'select 
        a.id,
        ''approver'' || row_number() over (partition by a.id order by p.personid) as col_name,
        c.fname
    from
        autoapprovalapproverconfig a
    left join autoapprovalapproverconfigmandate a2 on a2.autoapprovalaconfigurationid = a.id
    left join person p on p.personid = a2.approverid
    left join person_contact pc on pc.person_personid = p.personid
    left join public.clientcontact c on c.contact_id = pc.personaldetails_contact_id
    order by a.id',
    'values (''approver1''), (''approver2''), (''approver3'')'
) as ct(id int, approver1 text, approver2 text, approver3 text)
order by id;

说明 #

• 方法1无需依赖任何扩展，兼容性更强，适合大多数场景；
• 方法2的crosstab更适合复杂的行转列需求，但需要提前安装扩展；
• 两个方法中的order by子句可以根据实际需求调整排序逻辑（比如按审批人姓名排序）。

内容的提问来源于stack exchange，提问作者Richard