PostgreSQL使用Crosstab或其他SQL实现行转列的报错问题

原数据表 #

id|col_name|value|
--+--------+-----+
 1|col1    |ABC  |
 2|col2    |DEF  |
 2|col2    |FGH  |
 2|col2    |IJK  |
 3|col3    |MNO  |
 3|col3    |PQR  |
 3|col3    |STU  |
 3|col3    |XYZ  |

期望输出 #

id  Col1  Col2  col3
1    ABC   NULL  NULL
2    NULL  DEF   NULL
2    NULL  FGH   NULL
2    NULL  IJK   NULL
3    NULL  NULL  MNO
3    NULL  NULL  PQR
3    NULL  NULL  STU
3    NULL  NULL  XYZ

问题原因 #

你使用的crosstab查询报错，核心原因是标准crosstab要求输入的数据集必须是严格的"分组键-类别-值"结构，且每个分组下的所有类别必须存在，同时每个类别对应的行数一致。但原数据中不同id对应的col_name数量、行数差异极大（比如id=1只有col1的1行，id=2只有col2的3行），导致crosstab无法匹配定义的返回列结构，因此抛出return and sql tuple descriptions are incompatible错误。

解决方案1：修正crosstab用法（需依赖tablefunc扩展） #

先给每个id+col_name的组合生成行号，将行号作为第二个分组维度，确保每个(id, row_num)分组下的每个col_name最多对应1个值，让crosstab可以正确对齐列。

1. 确保安装tablefunc扩展（首次使用时执行）：

CREATE EXTENSION IF NOT EXISTS tablefunc;

2. 执行行转列查询：

SELECT id, col1, col2, col3
FROM crosstab(
    -- 子查询：为每个id+col_name的行生成行号，确保每个(row_num, id)下的col_name唯一
    'SELECT id || ''_'' || row_num AS group_key, col_name, value
     FROM (
         SELECT id, col_name, value,
                ROW_NUMBER() OVER (PARTITION BY id, col_name ORDER BY value) AS row_num
         FROM hr.temp
     ) t
     ORDER BY group_key',
    -- 指定需要转列的所有col_name
    'SELECT unnest(''{col1,col2,col3}''::text[])'
) AS final_result(
    group_key text, col1 TEXT, col2 TEXT, col3 TEXT
)
-- 拆分group_key还原id和行号，再排序
CROSS JOIN LATERAL (
    SELECT split_part(group_key, '_', 1)::int AS id,
           split_part(group_key, '_', 2)::int AS row_num
) AS g
ORDER BY id, row_num;

解决方案2：条件聚合+窗口函数（无需扩展，兼容性更强） #

这种方法不需要依赖crosstab，通过生成行号序列+条件聚合实现行转列，逻辑更直观：

WITH -- 第一步：计算每个id下各col_name的最大行数
col_row_counts AS (
    SELECT id, col_name, COUNT(*) AS row_count
    FROM hr.temp
    GROUP BY id, col_name
),
-- 第二步：计算每个id需要生成的总行数（取该id下所有col_name的最大行数）
id_max_rows AS (
    SELECT id, MAX(row_count) AS max_row
    FROM col_row_counts
    GROUP BY id
),
-- 第三步：为每个id生成对应行数的行号序列
id_row_numbers AS (
    SELECT id, generate_series(1, max_row) AS rn
    FROM id_max_rows
)
-- 第四步：左连接原数据，用条件聚合匹配每个行号对应的列值
SELECT
    irn.id,
    MAX(CASE WHEN t.col_name = 'col1' THEN t.value END) AS col1,
    MAX(CASE WHEN t.col_name = 'col2' THEN t.value END) AS col2,
    MAX(CASE WHEN t.col_name = 'col3' THEN t.value END) AS col3
FROM id_row_numbers irn
LEFT JOIN hr.temp t
    ON irn.id = t.id
    AND ROW_NUMBER() OVER (PARTITION BY t.id, t.col_name ORDER BY t.value) = irn.rn
GROUP BY irn.id, irn.rn
ORDER BY irn.id, irn.rn;

这个查询会生成符合预期的结果：每个id下生成足够的行（对应该id下最多的列值行数），每行对应各列的一个值，没有值的位置填充NULL。

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