PostgreSQL实现按分隔符拆分字符串并生成对应列的技术需求

假设你的表名为pathway_table，结构如下：

要实现将pathway列中的多值（竖线分隔、顺序随机）转换为单独的列，每个路径值对应一个pathway_xxx列，存在则显示值否则为NULL，可以用动态SQL+聚合的方式实现，适配任意数量的路径值：

完整SQL实现（动态适配所有路径值） #

DO $$
DECLARE
    pivot_columns text;
BEGIN
    -- 提取所有唯一路径值并生成列定义
    SELECT string_agg(
        format(
            'MAX(CASE WHEN trimmed_path = ''%s'' THEN ''%s'' END) AS pathway_%I',
            val, val, val
        ),
        ', '
    ) INTO pivot_columns
    FROM (
        SELECT DISTINCT trim(unnest(string_to_array(pathway, '|'))) AS val
        FROM pathway_table
        WHERE pathway IS NOT NULL
    ) AS unique_paths;

    -- 处理无路径值的边界情况
    IF pivot_columns IS NULL THEN
        pivot_columns = 'NULL AS no_pathways';
    END IF;

    -- 执行动态透视SQL
    EXECUTE format(
        'SELECT t.id, %s
         FROM (
             SELECT id, trim(unnest(string_to_array(pathway, ''|''))) AS trimmed_path
             FROM pathway_table
             WHERE pathway IS NOT NULL
         ) AS sub
         RIGHT JOIN pathway_table t ON sub.id = t.id
         GROUP BY t.id
         ORDER BY t.id;',
        pivot_columns
    );
END $$;

代码说明 #

1. 拆分与去重：通过string_to_array拆分pathway列，unnest将数组转为行，trim去除值前后的空格，DISTINCT提取所有唯一路径值。
2. 动态生成列：用string_agg和format拼接出每个路径值对应的CASE语句，自动生成pathway_A、pathway_B这类列名。
3. 保留所有行：通过RIGHT JOIN关联原表，确保即使pathway为NULL的行（如id=3）也能被保留，所有列值为NULL。
4. 聚合合并行：拆分后同一id会生成多行数据，用MAX聚合将同一id的行合并，保留对应路径值。

注意事项 #

• 自动适配任意数量的路径值，无需手动指定A、B、C等。
• 处理了路径值中的空格、NULL等异常情况。
• 如果路径值包含特殊字符（如空格、引号），%I格式符会自动转义列名，避免语法错误。

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