PostgreSQL Crosstab函数使用求助：实现两种数据透视需求

原始数据表 #

第一种目标格式：系统作为列名 #

要实现这种列转行效果，PostgreSQL中可以使用crosstab函数，需先启用tablefunc扩展：

步骤1：启用扩展 #

CREATE EXTENSION IF NOT EXISTS tablefunc;

步骤2：执行转换查询 #

将your_table_name替换为实际表名：

SELECT *
FROM crosstab(
    -- 源数据查询，按项目、日期、系统排序
    'SELECT project, date, system, COALESCE(result, '''')
     FROM your_table_name
     ORDER BY 1, 2, 3',
    -- 指定所有系统列（按字母顺序）
    'SELECT DISTINCT system FROM your_table_name ORDER BY 1'
) AS ct(
    "项目（Project)" text,
    "日期（Date)" text,
    "A" text,
    "B" text,
    "C" text,
    "D" text,
    "E" text,
    "F" text
);

• 使用COALESCE(result, '''')将NULL转为空字符串，匹配目标格式的空单元格。
• 第二个crosstab参数指定了所有要作为列的系统值，确保列顺序与目标一致。

第二种目标格式：系统+结果拼接为列值 #

这种格式需要先给每个项目+日期分组内的系统按字母排序，再将拼接值放入对应序号列，可通过条件聚合或crosstab实现：

方法1：条件聚合（无需扩展） #

SELECT
    project AS "项目（Project)",
    date AS "日期（Date)",
    MAX(CASE WHEN rn = 1 THEN CONCAT(system, '-', result) END) AS "System1",
    MAX(CASE WHEN rn = 2 THEN CONCAT(system, '-', result) END) AS "System2",
    MAX(CASE WHEN rn = 3 THEN CONCAT(system, '-', result) END) AS "System3",
    MAX(CASE WHEN rn = 4 THEN CONCAT(system, '-', result) END) AS "System4"
FROM (
    -- 给每个分组内的系统按字母排序，生成序号
    SELECT
        project,
        date,
        system,
        result,
        ROW_NUMBER() OVER (PARTITION BY project, date ORDER BY system) AS rn
    FROM your_table_name
) t
GROUP BY project, date
ORDER BY project, date;

方法2：使用crosstab函数 #

SELECT *
FROM crosstab(
    -- 生成带序号的拼接数据
    'SELECT project, date, rn, CONCAT(system, '-', result)
     FROM (
         SELECT
             project,
             date,
             system,
             result,
             ROW_NUMBER() OVER (PARTITION BY project, date ORDER BY system) AS rn
         FROM your_table_name
     ) t
     ORDER BY 1, 2, 3',
    -- 指定序号范围（当前数据最大分组有4个系统）
    'SELECT generate_series(1, 4)'
) AS ct(
    "项目（Project)" text,
    "日期（Date)" text,
    "System1" text,
    "System2" text,
    "System3" text,
    "System4" text
);

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