如何在AWS Athena中将多行转单行多列？查询问题排查

首先来说说你原来查询的问题出在哪：

• 你用map_agg(claim, role_clai)的时候，因为GROUP BY已经包含了claim字段，所以每个分组里的claim值是完全相同的。这时候用claim作为map的key，重复的key会被覆盖，最终map里只会保留该分组最后一条记录的role_clai值。
• 更关键的是，你后续用kv1['CLAI']取值，但map的key其实是实际的claim值（比如'00600000000015609'），而不是字符串'CLAI'，所以自然取不到任何值。

要实现把同一claim下的多条role_clai转成role_clai1、role_clai2这类多列的需求，正确的思路是先给每个分组内的role_clai记录分配一个序号，再通过条件聚合或者PIVOT来转列，下面给你两种可行的方案：

方案一：条件聚合（兼容绝大多数SQL引擎） #

这种方法不需要依赖特定的PIVOT语法，通用性很强：

SELECT 
    client, active, claim, role_polh, role_agnt,
    -- 按序号提取对应位置的role_clai值
    MAX(CASE WHEN rn = 1 THEN role_clai END) AS role_clai1,
    MAX(CASE WHEN rn = 2 THEN role_clai END) AS role_clai2,
    MAX(CASE WHEN rn = 3 THEN role_clai END) AS role_clai3 -- 如果有更多记录，可以继续增加这类行
FROM (
    -- 先给同一分组内的每条role_clai记录分配序号
    SELECT 
        client, active, claim, role_polh, role_agnt, role_clai,
        ROW_NUMBER() OVER (
            PARTITION BY client, active, claim, role_polh, role_agnt 
            ORDER BY role_clai -- 这里可以根据实际需求调整排序规则，比如按创建时间等
        ) AS rn
    FROM "final_view"
    WHERE claim = '00600000000015609'
) t
GROUP BY client, active, claim, role_polh, role_agnt

方案二：使用PIVOT（适用于支持该语法的引擎，如PostgreSQL 11+、BigQuery等） #

如果你的SQL引擎支持PIVOT，可以用更简洁的写法：

SELECT *
FROM (
    SELECT 
        client, active, claim, role_polh, role_agnt, role_clai,
        -- 生成目标列名，比如role_clai1、role_clai2
        'role_clai' || ROW_NUMBER() OVER (
            PARTITION BY client, active, claim, role_polh, role_agnt 
            ORDER BY role_clai
        ) AS col_name
    FROM "final_view"
    WHERE claim = '00600000000015609'
) t
PIVOT (
    MAX(role_clai) FOR col_name IN ('role_clai1', 'role_clai2', 'role_clai3') -- 列出需要生成的列名
) p

这两种方案都能把同一分组下的多条role_clai记录分别放到对应的role_claiN列中，你可以根据自己使用的SQL引擎选择合适的写法。

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