Postgres如何行转列查询租赁合同下的所有承租人姓名

推荐使用条件聚合实现行转列，比crosstab写法更通用、不需要额外依赖扩展，适配你使用的Postgres 10.18版本，执行效率也更高。

完整查询SQL #

SELECT
  r.id AS "Id",
  MAX(CASE WHEN t.rn = 1 THEN t.full_name END) AS "tenant 1",
  MAX(CASE WHEN t.rn = 2 THEN t.full_name END) AS "tenant 2",
  MAX(CASE WHEN t.rn = 3 THEN t.full_name END) AS "tenant 3",
  MAX(CASE WHEN t.rn = 4 THEN t.full_name END) AS "tenant 4"
FROM Rental r
LEFT JOIN (
  -- 合并主承租人和额外承租人，给每个承租人分配顺序编号
  SELECT
    rental_id,
    rn,
    CONCAT(p.first_name, ' ', p.last_name) AS full_name
  FROM (
    -- 主承租人固定排第1位
    SELECT id AS rental_id, Main_tenant_id AS tenant_id, 1 AS rn FROM Rental
    UNION ALL
    -- 额外承租人按关联表ID升序，依次排第2、3、4位
    SELECT 
      rental_id, 
      xtra_tenant AS tenant_id,
      ROW_NUMBER() OVER (PARTITION BY rental_id ORDER BY id) + 1 AS rn 
    FROM Rental_extra
  ) all_tenant
  JOIN Person p ON all_tenant.tenant_id = p.id
  WHERE rn <= 4 -- 最多保留4个承租人
) t ON r.id = t.rental_id
GROUP BY r.id
ORDER BY r.id;

方案说明 #

• 排序规则可自定义：如果需要调整额外承租人的排序逻辑，只需要修改ROW_NUMBER()对应的ORDER BY部分即可
• 扩展性强：后续如果需要支持更多承租人列，只需要新增对应序号的CASE WHEN聚合行即可
• 不需要依赖tablefunc扩展，避免crosstab经常出现的列类型不匹配、返回结构定义错误等问题

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