Oracle SQL实现行列互转问题求解（附原表与期望结果）

以下SQL默认你的源存储表名为gst_source，实际使用时替换为业务真实表名即可。

方法1：Oracle 11g及以上版本原生函数写法（推荐） #

逻辑分两步：先用UNPIVOT把源表中HOME_GST/DELAY/DEFAULT三个指标列转为行存储，再用PIVOT把GSTNO的枚举值转为结果列，最后排序保证行顺序和预期一致。

SELECT *
FROM (
    SELECT gstno, metric_name, metric_value
    FROM gst_source
    UNPIVOT (
        metric_value 
        FOR metric_name IN (HOME_GST, DELAY, "DEFAULT")
    )
)
PIVOT (
    MAX(metric_value)
    FOR gstno IN ('GST1' AS GST1, 'GST2' AS GST2, 'GST3' AS GST3, 'GST4' AS GST4)
)
ORDER BY 
    CASE metric_name 
        WHEN 'HOME_GST' THEN 1 
        WHEN 'DELAY' THEN 2 
        WHEN 'DEFAULT' THEN 3 
    END;

注意：DEFAULT是Oracle保留关键字，在SQL中引用该列时必须加双引号，否则会触发语法错误

方法2：低版本兼容写法（支持Oracle 10g及更早版本） #

如果你的数据库版本不支持PIVOT/UNPIVOT函数，可以用UNION ALL完成列转行，CASE WHEN+聚合函数完成行转列，逻辑和原生写法完全一致：

SELECT 
    'HOME_GST' AS GSTNO,
    MAX(CASE WHEN gstno = 'GST1' THEN HOME_GST END) AS GST1,
    MAX(CASE WHEN gstno = 'GST2' THEN HOME_GST END) AS GST2,
    MAX(CASE WHEN gstno = 'GST3' THEN HOME_GST END) AS GST3,
    MAX(CASE WHEN gstno = 'GST4' THEN HOME_GST END) AS GST4
FROM gst_source
UNION ALL
SELECT 
    'DELAY' AS GSTNO,
    MAX(CASE WHEN gstno = 'GST1' THEN DELAY END) AS GST1,
    MAX(CASE WHEN gstno = 'GST2' THEN DELAY END) AS GST2,
    MAX(CASE WHEN gstno = 'GST3' THEN DELAY END) AS GST3,
    MAX(CASE WHEN gstno = 'GST4' THEN DELAY END) AS GST4
FROM gst_source
UNION ALL
SELECT 
    'DEFAULT' AS GSTNO,
    MAX(CASE WHEN gstno = 'GST1' THEN "DEFAULT" END) AS GST1,
    MAX(CASE WHEN gstno = 'GST2' THEN "DEFAULT" END) AS GST2,
    MAX(CASE WHEN gstno = 'GST3' THEN "DEFAULT" END) AS GST3,
    MAX(CASE WHEN gstno = 'GST4' THEN "DEFAULT" END) AS GST4
FROM gst_source
ORDER BY 
    CASE GSTNO 
        WHEN 'HOME_GST' THEN 1 
        WHEN 'DELAY' THEN 2 
        WHEN 'DEFAULT' THEN 3 
    END;

以上两段SQL执行后返回的结果完全匹配你给出的期望表结构，Y/N取值对应关系和示例完全一致。

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