如何编写PostgreSQL代码将示例数据表转换为目标结构

开发者阿泽

2026-8-3

PostgreSQL行转列实现表结构转换

假设你的源表包含id（唯一标识）、属性名（对应目标表的列名）、属性值三个核心字段，我们可以用PostgreSQL的crosstab函数实现行转列，把左侧长表结构转为右侧宽表结构。

步骤1：安装必要扩展

crosstab属于tablefunc扩展，需先安装：

CREATE EXTENSION IF NOT EXISTS tablefunc;

步骤2：固定属性列的转换方案

如果已知所有要转换的属性列名称，可直接写静态查询：

SELECT *
FROM crosstab(
    -- 源数据查询：按id和属性名排序
    'SELECT id, attribute_name, attribute_value
     FROM source_table
     ORDER BY 1, 2',
    -- 指定要转为列的属性列表
    'SELECT DISTINCT attribute_name
     FROM source_table
     ORDER BY 1'
) AS result_table (
    id INT, -- 唯一ID列
    "属性1" TEXT, -- 替换为实际属性名，类型按需调整
    "属性2" TEXT,
    "属性3" TEXT
    -- 继续添加所有需要转换的属性列
);

步骤3：动态属性列的转换方案

如果属性列不固定（实际有更多未知属性），用动态SQL自动生成列：

DO $$
DECLARE
    column_defs TEXT;
BEGIN
    -- 自动拼接所有属性的列定义
    SELECT string_agg(DISTINCT quote_ident(attribute_name) || ' TEXT', ', ')
    INTO column_defs
    FROM source_table;

    -- 执行动态行转列查询
    EXECUTE format('
        SELECT *
        FROM crosstab(
            ''SELECT id, attribute_name, attribute_value FROM source_table ORDER BY 1, 2'',
            ''SELECT DISTINCT attribute_name FROM source_table ORDER BY 1''
        ) AS result_table (id INT, %s)
    ', column_defs);
END $$;