PostgreSQL行转列求助：含重复Type值的多列转置实现

前提假设表结构 #

先明确两张表的基础结构（如果你的表字段不同，自行替换）：

-- Table1：订单与订单行表
CREATE TABLE table1 (
    order_id INT,
    line_id INT PRIMARY KEY
);

-- Table2：订单行的Type及金额表
CREATE TABLE table2 (
    line_id INT REFERENCES table1(line_id),
    type TEXT,
    amount NUMERIC,
    PRIMARY KEY (line_id)
);

步骤1：给同订单同Type的行添加序号 #

先通过关联查询，给每个订单下的相同Type行分配序号（比如APPLE的第1行、第2行）：

SELECT
    t1.order_id,
    t2.type,
    t2.amount,
    -- 按订单+Type分区，给每行加序号，排序依据按需调整（这里用line_id）
    ROW_NUMBER() OVER (PARTITION BY t1.order_id, t2.type ORDER BY t2.line_id) AS rn
FROM table1 t1
JOIN table2 t2 ON t1.line_id = t2.line_id;

方案1：静态列名（已知最大序号） #

如果已经明确每个Type最多出现的次数（比如最多2次），可以直接用crosstab静态实现：

SELECT * FROM crosstab(
    -- 数据源：拼接Type和序号作为列标识
    'SELECT
        order_id,
        type || rn,
        amount
     FROM (
         SELECT
             t1.order_id,
             t2.type,
             t2.amount,
             ROW_NUMBER() OVER (PARTITION BY t1.order_id, t2.type ORDER BY t2.line_id) AS rn
         FROM table1 t1
         JOIN table2 t2 ON t1.line_id = t2.line_id
     ) sub
     ORDER BY 1, 2',
    -- 指定目标列的列表
    'SELECT unnest(ARRAY[''APPLE1'', ''APPLE2'', ''BANANA1'', ''BANANA2''])'
) AS ct (
    order_id INT,
    APPLE1 NUMERIC,
    APPLE2 NUMERIC,
    BANANA1 NUMERIC,
    BANANA2 NUMERIC
);

方案2：动态生成列名（适配任意次数的重复Type） #

如果无法确定每个Type的最大出现次数，用动态SQL自动生成所有需要的列：

DO $$
DECLARE
    cols TEXT;
    col_list TEXT;
BEGIN
    -- 生成所有唯一的目标列名（格式：Type+序号）
    SELECT string_agg(DISTINCT quote_ident(type || rn), ', ')
    INTO cols
    FROM (
        SELECT
            t2.type,
            ROW_NUMBER() OVER (PARTITION BY t1.order_id, t2.type ORDER BY t2.line_id) AS rn
        FROM table1 t1
        JOIN table2 t2 ON t1.line_id = t2.line_id
    ) sub;

    -- 拼接crosstab的列定义
    col_list := 'order_id INT, ' || cols;

    -- 执行动态crosstab查询
    EXECUTE format('
        SELECT * FROM crosstab(
            ''SELECT
                order_id,
                type || rn,
                amount
             FROM (
                 SELECT
                     t1.order_id,
                     t2.type,
                     t2.amount,
                     ROW_NUMBER() OVER (PARTITION BY t1.order_id, t2.type ORDER BY t2.line_id) AS rn
                 FROM table1 t1
                 JOIN table2 t2 ON t1.line_id = t2.line_id
             ) sub
             ORDER BY 1, 2'',
            ''SELECT unnest(ARRAY[%L])''
        ) AS ct (%s)',
        array(SELECT DISTINCT type || rn FROM (
            SELECT
                t2.type,
                ROW_NUMBER() OVER (PARTITION BY t1.order_id, t2.type ORDER BY t2.line_id) AS rn
            FROM table1 t1
            JOIN table2 t2 ON t1.line_id = t2.line_id
        ) sub),
        col_list
    );
END $$;

注意事项 #

• ROW_NUMBER()的ORDER BY子句要根据业务需求调整（比如按金额、创建时间排序）
• 用quote_ident确保列名包含特殊字符时不会报错
• 动态SQL需要当前用户有执行权限
• 如果需要保留NULL值，crosstab(text, text)的形式会比单参数的crosstab更可靠，能确保所有目标列都被返回

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