如何在不使用UNION ALL的情况下实现SQL多列转多行？

要把tableA中key_value（原查询里的key value应为笔误，统一用key_value）搭配多组AttributeX/ValueX的多列结果，转成每行对应一组属性和值的多行结构，不用UNION ALL的话，不同数据库都有原生高效方法——这类方法仅扫描一次表，比UNION ALL多次扫描的性能提升明显，以下是主流数据库的实现方案：

SQL Server / Azure SQL #

方案1：原生UNPIVOT（2022+版本支持多列映射） #

SELECT 
    key_value,
    attribute_name,
    attribute_value
FROM (
    SELECT 
        key_value,
        attribute1, value1,
        attribute2, value2,
        attribute3, value3,
        attribute4, value4,
        attribute5, value5
    FROM tableA
) src
UNPIVOT (
    attribute_value FOR attribute_name IN (
        (attribute1, value1),
        (attribute2, value2),
        (attribute3, value3),
        (attribute4, value4),
        (attribute5, value5)
    )
) unpvt;

方案2：CROSS APPLY + VALUES（兼容低版本） #

如果你的SQL Server版本低于2022，用这个更稳妥：

SELECT 
    key_value,
    attribute_name,
    attribute_value
FROM tableA
CROSS APPLY (
    VALUES
        ('attribute1', attribute1),
        ('attribute2', attribute2),
        ('attribute3', attribute3),
        ('attribute4', attribute4),
        ('attribute5', attribute5)
) ca(attribute_name, attribute_value);

PostgreSQL #

方案1：UNNEST数组映射 #

SELECT 
    key_value,
    unnest(array['attribute1', 'attribute2', 'attribute3', 'attribute4', 'attribute5']) AS attribute_name,
    unnest(array[value1, value2, value3, value4, value5]) AS attribute_value
FROM tableA;

方案2：LATERAL JOIN + VALUES #

可读性更好，性能稳定：

SELECT 
    a.key_value,
    b.attribute_name,
    b.attribute_value
FROM tableA a
LATERAL (
    VALUES
        ('attribute1', a.value1),
        ('attribute2', a.value2),
        ('attribute3', a.value3),
        ('attribute4', a.value4),
        ('attribute5', a.value5)
) b(attribute_name, attribute_value);

MySQL 8.0+ / MariaDB #

方案1：JSON_TABLE（推荐） #

利用JSON构造数组再解析，性能优于UNION ALL：

SELECT 
    a.key_value,
    jt.attribute_name,
    jt.attribute_value
FROM tableA a
JOIN JSON_TABLE(
    JSON_ARRAY(
        JSON_OBJECT('name', 'attribute1', 'value', a.value1),
        JSON_OBJECT('name', 'attribute2', 'value', a.value2),
        JSON_OBJECT('name', 'attribute3', 'value', a.value3),
        JSON_OBJECT('name', 'attribute4', 'value', a.value4),
        JSON_OBJECT('name', 'attribute5', 'value', a.value5)
    ),
    '$[*]' COLUMNS (
        attribute_name VARCHAR(50) PATH '$.name',
        attribute_value VARCHAR(255) PATH '$.value'
    )
) jt;

方案2：CROSS JOIN + VALUES（8.0.19+支持） #

SELECT 
    a.key_value,
    b.attribute_name,
    b.attribute_value
FROM tableA a
CROSS JOIN (
    VALUES
        ('attribute1', a.value1),
        ('attribute2', a.value2),
        ('attribute3', a.value3),
        ('attribute4', a.value4),
        ('attribute5', a.value5)
) b(attribute_name, attribute_value);

Oracle #

方案1：原生UNPIVOT #

SELECT 
    key_value,
    attribute_name,
    attribute_value
FROM (
    SELECT 
        key_value,
        attribute1, value1,
        attribute2, value2,
        attribute3, value3,
        attribute4, value4,
        attribute5, value5
    FROM tableA
)
UNPIVOT (
    attribute_value FOR attribute_name IN (
        attribute1 AS 'attribute1',
        attribute2 AS 'attribute2',
        attribute3 AS 'attribute3',
        attribute4 AS 'attribute4',
        attribute5 AS 'attribute5'
    )
);

方案2：LATERAL JOIN（12c+支持） #

SELECT 
    a.key_value,
    b.attribute_name,
    b.attribute_value
FROM tableA a
CROSS JOIN LATERAL (
    SELECT 'attribute1' AS attribute_name, a.attribute1 AS attribute_value FROM DUAL
    UNION ALL
    SELECT 'attribute2' AS attribute_name, a.attribute2 AS attribute_value FROM DUAL
    UNION ALL
    SELECT 'attribute3' AS attribute_name, a.attribute3 AS attribute_value FROM DUAL
    UNION ALL
    SELECT 'attribute4' AS attribute_name, a.attribute4 AS attribute_value FROM DUAL
    UNION ALL
    SELECT 'attribute5' AS attribute_name, a.attribute5 AS attribute_value FROM DUAL
) b;

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