PostgreSQL如何将列值转换为行？请求技术解决方案

你需要将单行多列的数据转换为多行键值对形式，这在SQL中称为Unpivot操作。以下是针对不同数据库的实现方案：

MySQL/MariaDB #

方法1：使用UNION ALL（兼容性好） #

适合所有版本的MySQL，需手动列出每个列：

SELECT 'id' AS column_key, id AS column_value FROM profile_change_set WHERE id = '1558079b-d954-4a0d-b241-b39fe8f3498c'
UNION ALL
SELECT 'table_name' AS column_key, table_name AS column_value FROM profile_change_set WHERE id = '1558079b-d954-4a0d-b241-b39fe8f3498c'
UNION ALL
SELECT 'column_key' AS column_key, column_key AS column_value FROM profile_change_set WHERE id = '1558079b-d954-4a0d-b241-b39fe8f3498c'
UNION ALL
SELECT 'column_value' AS column_key, column_value AS column_value FROM profile_change_set WHERE id = '1558079b-d954-4a0d-b241-b39fe8f3498c'
UNION ALL
SELECT 'operation_type' AS column_key, operation_type AS column_value FROM profile_change_set WHERE id = '1558079b-d954-4a0d-b241-b39fe8f3498c';

方法2：使用JSON_TABLE（MySQL 8.0+） #

无需硬编码列名，利用JSON函数动态转换：

SELECT j.column_key, j.column_value
FROM profile_change_set p,
     JSON_TABLE(
         JSON_OBJECT(
             'id', p.id,
             'table_name', p.table_name,
             'column_key', p.column_key,
             'column_value', p.column_value,
             'operation_type', p.operation_type
         ),
         '$.*' COLUMNS(
             column_key VARCHAR(255) PATH '$',
             column_value VARCHAR(255) PATH '$'
         )
     ) j
WHERE p.id = '1558079b-d954-4a0d-b241-b39fe8f3498c';

PostgreSQL #

方法1：使用UNION ALL #

SELECT 'id' AS column_key, id::text AS column_value FROM profile_change_set WHERE id = '1558079b-d954-4a0d-b241-b39fe8f3498c'
UNION ALL
SELECT 'table_name' AS column_key, table_name::text AS column_value FROM profile_change_set WHERE id = '1558079b-d954-4a0d-b241-b39fe8f3498c'
UNION ALL
SELECT 'column_key' AS column_key, column_key::text AS column_value FROM profile_change_set WHERE id = '1558079b-d954-4a0d-b241-b39fe8f3498c'
UNION ALL
SELECT 'column_value' AS column_key, column_value::text AS column_value FROM profile_change_set WHERE id = '1558079b-d954-4a0d-b241-b39fe8f3498c'
UNION ALL
SELECT 'operation_type' AS column_key, operation_type::text AS column_value FROM profile_change_set WHERE id = '1558079b-d954-4a0d-b241-b39fe8f3498c';

方法2：使用jsonb_each_text（动态转换） #

自动遍历所有列，无需硬编码：

SELECT key AS column_key, value AS column_value
FROM profile_change_set p,
     jsonb_each_text(to_jsonb(p))
WHERE p.id = '1558079b-d954-4a0d-b241-b39fe8f3498c';

SQL Server #

使用原生UNPIVOT语法：

SELECT column_key, column_value
FROM (
    SELECT 
        CAST(id AS VARCHAR(MAX)) AS id,
        table_name,
        column_key,
        column_value,
        operation_type
    FROM profile_change_set
    WHERE id = '1558079b-d954-4a0d-b241-b39fe8f3498c'
) t
UNPIVOT (
    column_value FOR column_key IN (id, table_name, column_key, column_value, operation_type)
) up;

内容的提问来源于stack exchange，提问作者M. Hamza Rajput