Oracle多表与单表的Pivot行转列实现方法咨询

先将三个表通过id和seq关联得到完整数据集，再用PIVOT对seq进行转置：

SELECT id,
       MFR_1 AS Mfr1,
       MFR_2 AS Mfr2,
       FLAG_1 AS flag1,
       FLAG_2 AS flag2,
       PN_1 AS pn1,
       PN_2 AS pn2
FROM (
    -- 关联三表，获取每个(id, seq)对应的所有字段值
    SELECT mf.id,
           mf.seq,
           mf.mfr,
           flg.flag,
           pn.pn
    FROM mf_tab mf
    INNER JOIN flg_tab flg 
        ON mf.id = flg.id 
        AND mf.seq = flg.seq
    INNER JOIN pn_tab pn 
        ON mf.id = pn.id 
        AND mf.seq = pn.seq
) src
PIVOT (
    -- 聚合对应字段（因(id,seq)唯一，MAX/ANY_VALUE均可）
    MAX(mfr) AS MFR,
    MAX(flag) AS FLAG,
    MAX(pn) AS PN
    FOR seq IN (1 AS 1, 2 AS 2)
) piv;

• 若存在seq>2的情况，只需在FOR seq IN中添加对应数值，同时在SELECT列表补充对应字段即可。
• Oracle 12c+可使用ANY_VALUE()替代MAX()，语义更贴合唯一值场景。

假设单表名为combined_tab，包含id、seq、mfr、flag、pn字段，直接基于单表执行Pivot：

SELECT id,
       MFR_1 AS Mfr1,
       MFR_2 AS Mfr2,
       FLAG_1 AS flag1,
       FLAG_2 AS flag2,
       PN_1 AS pn1,
       PN_2 AS pn2
FROM combined_tab
PIVOT (
    MAX(mfr) AS MFR,
    MAX(flag) AS FLAG,
    MAX(pn) AS PN
    FOR seq IN (1 AS 1, 2 AS 2)
) piv;

如果需要保留单表Pivot的操作逻辑，可分别对每个表转置后通过id关联：

SELECT mf.id,
       mf.Mfr1,
       mf.Mfr2,
       flg.flag1,
       flg.flag2,
       pn.pn1,
       pn.pn2
FROM (
    SELECT id, MFR_1 AS Mfr1, MFR_2 AS Mfr2
    FROM mf_tab
    PIVOT (MAX(mfr) AS MFR FOR seq IN (1 AS 1, 2 AS 2))
) mf
INNER JOIN (
    SELECT id, FLAG_1 AS flag1, FLAG_2 AS flag2
    FROM flg_tab
    PIVOT (MAX(flag) AS FLAG FOR seq IN (1 AS 1, 2 AS 2))
) flg ON mf.id = flg.id
INNER JOIN (
    SELECT id, PN_1 AS pn1, PN_2 AS pn2
    FROM pn_tab
    PIVOT (MAX(pn) AS PN FOR seq IN (1 AS 1, 2 AS 2))
) pn ON mf.id = pn.id;

• 此方案适合三表数据量差异大、需单独处理单表转置逻辑的场景，但性能略逊于先关联再转置的方式。

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