如何基于字符串前缀对两组列执行SQL UNPIVOT操作

通用兼容方案（适用于所有SQL数据库） #

直接通过UNION ALL将每组value_n和type_n对应的行拼接，语法简单且兼容性拉满：

SELECT id, type_0 AS parameter, value_0 AS category
FROM 原表
-- 可选：过滤空参数行
WHERE type_0 IS NOT NULL
UNION ALL
SELECT id, type_1 AS parameter, value_1 AS category
FROM 原表
WHERE type_1 IS NOT NULL
UNION ALL
SELECT id, type_2 AS parameter, value_2 AS category
FROM 原表
WHERE type_2 IS NOT NULL
UNION ALL
SELECT id, type_3 AS parameter, value_3 AS category
FROM 原表
WHERE type_3 IS NOT NULL;

针对支持UNPIVOT的数据库（如SQL Server、Oracle） #

先分别对value列和type列做UNPIVOT，再通过后缀关联匹配：

WITH value_unpivot AS (
    SELECT 
        id,
        -- 提取后缀数字用于关联
        REPLACE(suffix, 'value_', '') AS num,
        category
    FROM 原表
    UNPIVOT (
        category FOR suffix IN (value_0, value_1, value_2, value_3)
    ) AS up
),
type_unpivot AS (
    SELECT 
        id,
        REPLACE(suffix, 'type_', '') AS num,
        parameter
    FROM 原表
    UNPIVOT (
        parameter FOR suffix IN (type_0, type_1, type_2, type_3)
    ) AS up
)
SELECT v.id, t.parameter, v.category
FROM value_unpivot v
JOIN type_unpivot t ON v.id = t.id AND v.num = t.num;

针对PostgreSQL #

利用数组和unnest函数实现横向展开：

SELECT 
    t.id,
    p.parameter,
    p.category
FROM 原表 t
LATERAL (
    SELECT 
        unnest(array[t.type_0, t.type_1, t.type_2, t.type_3]) AS parameter,
        unnest(array[t.value_0, t.value_1, t.value_2, t.value_3]) AS category
) p
-- 可选：过滤空参数行
WHERE p.parameter IS NOT NULL;

针对MySQL 8.0+ #

借助JSON_TABLE函数实现转换：

SELECT 
    t.id,
    j.parameter,
    j.category
FROM 原表 t
JOIN JSON_TABLE(
    JSON_ARRAY(
        JSON_OBJECT('p', t.type_0, 'c', t.value_0),
        JSON_OBJECT('p', t.type_1, 'c', t.value_1),
        JSON_OBJECT('p', t.type_2, 'c', t.value_2),
        JSON_OBJECT('p', t.type_3, 'c', t.value_3)
    ),
    '$[*]' COLUMNS (
        parameter VARCHAR(255) PATH '$.p',
        category VARCHAR(255) PATH '$.c'
    )
) j
-- 可选：过滤空参数行
WHERE j.parameter IS NOT NULL;

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