Oracle多列UNPIVOT语句等效的Postgres实现方案咨询

Oracle多列UNPIVOT转Postgres实现 #

多列UNPIVOT的转换逻辑和你已经掌握的单列场景完全一致，只需要在LATERAL关联的VALUES列表中，每行对应展开的多组值即可，转换后的完整语句如下：

WITH cte AS (
    SELECT  
        MIN(EFFECTIVE_DATE) AS MIN_DATE,
        MAX(EFFECTIVE_DATE) AS MAX_DATE, 
        id,
        dt_ref.dp_id
    FROM idd_eom, dt_ref
    GROUP BY id, dt_ref.dp_id
)
SELECT 
    cte.id, 
    cte.dp_id, 
    s.dp_code, 
    s.min_date, 
    s.max_date
FROM cte
, LATERAL (
    VALUES('EFFECTIVE_DATE', MIN_DATE, MAX_DATE)
) s(dp_code, min_date, max_date);

扩展说明 #

1. 如果你偏好显式连接写法，可以把语句中的逗号换成CROSS JOIN LATERAL，可读性更强，执行效果完全一致
2. 当前写法天然兼容Oracle UNPIVOT INCLUDE NULLS的行为，Postgres的VALUES结构会保留NULL值，无需额外配置
3. 如果你后续需要扩展多个UNPIVOT的匹配项，只需要在VALUES后新增对应行即可，示例如下：

-- 对应Oracle多IN项的UNPIVOT
LATERAL (
    VALUES('EFFECTIVE_DATE', MIN_DATE, MAX_DATE),
          ('CREATE_DATE', CREATE_MIN, CREATE_MAX)
) s(dp_code, min_date, max_date)

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