如何在PostgreSQL中无需crosstab/tablefunc扩展实现矩阵转置

静态转置（已知目标列） #

如果已经明确转置后的列名（比如示例中的分类A、B），可以用CASE表达式配合聚合函数实现，这是最直接的方案：

假设输入表结构及示例数据如下：

CREATE TABLE input_data (
    category VARCHAR(50),
    metric VARCHAR(50),
    value NUMERIC
);

INSERT INTO input_data VALUES
('A', '销量', 100),
('A', '利润', 20),
('B', '销量', 150),
('B', '利润', 30);

执行以下SQL完成转置：

SELECT
    metric,
    MAX(CASE WHEN category = 'A' THEN value END) AS "A",
    MAX(CASE WHEN category = 'B' THEN value END) AS "B"
FROM input_data
GROUP BY metric
ORDER BY metric;

这里用MAX是因为每个(metric, category)组合仅对应一条数据，用SUM也能达到相同效果。

动态转置（未知目标列） #

如果分类category的取值会动态变化，无法提前确定列名，可以通过动态生成SQL实现：

方法1：生成可执行的转置SQL

WITH category_list AS (
    SELECT DISTINCT category FROM input_data
)
SELECT
    'SELECT metric, ' ||
    string_agg('MAX(CASE WHEN category = ''' || category || ''' THEN value END) AS "' || category || '"', ', ') ||
    ' FROM input_data GROUP BY metric ORDER BY metric;' AS transpose_sql
FROM category_list;

执行这段SQL会得到完整的转置语句，复制结果执行即可得到动态列的转置矩阵。

方法2：封装为PL/pgSQL函数（自动化场景）

如果需要在Slack自动化流程中直接调用，可以封装成函数返回结构化结果：

CREATE OR REPLACE FUNCTION transpose_input_data()
RETURNS TABLE (metric VARCHAR, category_values JSONB) AS $$
DECLARE
    sql TEXT;
BEGIN
    WITH category_list AS (
        SELECT DISTINCT category FROM input_data
    )
    SELECT
        'SELECT metric, jsonb_object_agg(category, value) AS category_values FROM input_data GROUP BY metric ORDER BY metric;'
    INTO sql FROM category_list;

    RETURN QUERY EXECUTE sql;
END;
$$ LANGUAGE plpgsql;

调用函数后会返回指标名和包含所有分类值的JSONB对象，便于后续格式化为Slack消息的可读样式。

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