Oracle SQL及Impala多行转列实现：保留多值并过滤指定ID

问题背景 #

源表数据 #

目标输出 #

需过滤domain_id为ID4的数据，且ID1/ID2/ID3各有2组对应值，普通PIVOT使用聚合函数会丢失行数据，需保留所有对应分组的行。

Oracle SQL 实现 #

核心思路是先给每个user_id + domain_id分组内的行添加序号，再基于序号做PIVOT，确保每组对应行正确匹配：

WITH ranked_data AS (
    SELECT 
        user_id,
        domain_id,
        id_value,
        id_status,
        -- 按用户和域名分组，生成行序号
        ROW_NUMBER() OVER (PARTITION BY user_id, domain_id ORDER BY id_value) AS rn
    FROM your_table
    WHERE domain_id IN ('ID1', 'ID2', 'ID3') -- 过滤ID4
)
SELECT 
    user_id,
    ID1,
    ID2,
    ID3,
    id_status
FROM ranked_data
PIVOT (
    MAX(id_value) -- 聚合函数仅用于提取对应序号的唯一值
    FOR domain_id IN ('ID1' AS ID1, 'ID2' AS ID2, 'ID3' AS ID3)
)
ORDER BY rn;

关键说明 #

• ROW_NUMBER()为每个user_id+domain_id组内的行生成唯一序号，保证同一序号下的ID1/ID2/ID3属于同一组对应行
• Oracle的PIVOT会自动将rn作为分组依据，避免行被聚合合并
• 提前在CTE中过滤ID4，减少后续计算量

Impala SQL 实现 #

Impala对PIVOT的复杂场景支持有限，采用CASE + GROUP BY的传统方式，配合序号实现需求：

WITH ranked_data AS (
    SELECT 
        user_id,
        domain_id,
        id_value,
        id_status,
        ROW_NUMBER() OVER (PARTITION BY user_id, domain_id ORDER BY id_value) AS rn
    FROM your_table
    WHERE domain_id IN ('ID1', 'ID2', 'ID3')
)
SELECT 
    user_id,
    MAX(CASE WHEN domain_id = 'ID1' THEN id_value END) AS ID1,
    MAX(CASE WHEN domain_id = 'ID2' THEN id_value END) AS ID2,
    MAX(CASE WHEN domain_id = 'ID3' THEN id_value END) AS ID3,
    id_status
FROM ranked_data
GROUP BY user_id, rn, id_status
ORDER BY rn;

关键说明 #

• 分组时必须包含rn，确保同一序号的ID1/ID2/ID3值被分到同一组，不会被聚合合并
• CASE语句实现列转行，MAX仅用于提取对应分组的唯一值
• 同样通过CTE提前过滤ID4并生成序号，保证数据准确性

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