横转纵与纵转横（Pivot）
High Contrast
Dark Mode
Light Mode
Sepia
Forest
2 min read372 words

横转纵与纵转横（Pivot）

报表开发中经常需要把"多行"变成"多列"（行转列，Pivot），或者把"多列"变成"多行"（列转行，Unpivot）。MySQL 有 GROUP_CONCAT，但 PostgreSQL 有更强大的工具。

行转列（纵转横）：CASE WHEN + GROUP BY

最通用的方式，不依赖任何扩展：

-- 原始数据：每个用户、每月的销售额（一月一行）
-- user_id | month | revenue
-- ---------|-------|--------
-- 1        | 2024-01 | 500
-- 1        | 2024-02 | 750
-- 2        | 2024-01 | 300
-- 目标：透视为每用户一行，每月一列
-- user_id | jan_revenue | feb_revenue | mar_revenue
SELECT
user_id,
SUM(CASE WHEN month = '2024-01' THEN revenue ELSE 0 END) AS jan_revenue,
SUM(CASE WHEN month = '2024-02' THEN revenue ELSE 0 END) AS feb_revenue,
SUM(CASE WHEN month = '2024-03' THEN revenue ELSE 0 END) AS mar_revenue
FROM monthly_revenue
GROUP BY user_id
ORDER BY user_id;

行转列：crosstab（tablefunc 扩展）

当列名是动态的，用 crosstab 更简洁（需要 tablefunc 扩展）：

-- 启用扩展
CREATE EXTENSION IF NOT EXISTS tablefunc;
-- 使用 crosstab（列名必须预先知道）
SELECT * FROM crosstab(
-- 第一个查询：数据查询（必须有3列：row_name, category, value）
$$
SELECT user_id::text, month, revenue
FROM monthly_revenue
ORDER BY user_id, month
$$,
-- 第二个查询：枚举所有可能的 category 值
$$VALUES ('2024-01'), ('2024-02'), ('2024-03')$$
) AS pivot_result (
user_id TEXT,
jan_revenue NUMERIC,
feb_revenue NUMERIC,
mar_revenue NUMERIC
);

列转行（横转纵）：UNNEST 和 VALUES

方法一：用 UNNEST 展开数组列

-- 如果有一列存储了数组：
-- id | tags
-- 1  | {electronics, laptop, sale}
SELECT id, unnest(tags) AS tag
FROM products;
-- 输出：
-- 1 | electronics
-- 1 | laptop
-- 1 | sale

方法二：用 VALUES 把列变行（固定列数）

-- 产品表有 price_usd, price_myr, price_sgd 三列
-- 目标：每种货币一行
SELECT p.id, v.currency, v.price
FROM products p
CROSS JOIN LATERAL (VALUES
('USD', p.price_usd),
('MYR', p.price_myr),
('SGD', p.price_sgd)
) AS v(currency, price)
WHERE v.price IS NOT NULL;

jsonb_each：JSONB 转多行

-- 把 JSONB 的键值对展开为行
SELECT
id,
key AS attribute_name,
value AS attribute_value
FROM products,
jsonb_each_text(attributes)  -- attributes 是 JSONB 列
ORDER BY id, key;
-- 示例 attributes: {"color": "red", "size": "M", "material": "cotton"}
-- 输出：
-- 1 | color    | red
-- 1 | size     | M
-- 1 | material | cotton

实战：生成月度销售透视报表

-- 完整场景：按产品分类统计各月销售额，横向展开为月份列
WITH monthly_category_sales AS (
SELECT
DATE_TRUNC('month', o.created_at)::date AS month,
c.name AS category,
SUM(oi.unit_price * oi.quantity) AS revenue
FROM order_items oi
JOIN orders o ON oi.order_id = o.id
JOIN products p ON oi.product_id = p.id
JOIN categories c ON p.category_id = c.id
WHERE o.status = 'paid'
AND o.created_at >= '2024-01-01'
AND o.created_at < '2024-07-01'
GROUP BY 1, 2
)
SELECT
category,
SUM(CASE WHEN month = '2024-01-01' THEN revenue ELSE 0 END) AS "Jan",
SUM(CASE WHEN month = '2024-02-01' THEN revenue ELSE 0 END) AS "Feb",
SUM(CASE WHEN month = '2024-03-01' THEN revenue ELSE 0 END) AS "Mar",
SUM(CASE WHEN month = '2024-04-01' THEN revenue ELSE 0 END) AS "Apr",
SUM(CASE WHEN month = '2024-05-01' THEN revenue ELSE 0 END) AS "May",
SUM(CASE WHEN month = '2024-06-01' THEN revenue ELSE 0 END) AS "Jun",
SUM(revenue) AS "Total"
FROM monthly_category_sales
GROUP BY category
ORDER BY "Total" DESC;

动态列数的行转列（应用层生成 SQL）

当列是动态的（不知道有多少个），需要在应用层动态生成 SQL：

-- 第一步：查出所有可能的月份
SELECT DISTINCT DATE_TRUNC('month', created_at)::date
FROM orders
ORDER BY 1;
-- 第二步：在应用层（Python/Node.js）根据结果动态生成 CASE WHEN 语句
-- 第三步：执行动态生成的 SQL

PostgreSQL 本身没有原生的动态列透视功能（Oracle 有 PIVOT/UNPIVOT，PostgreSQL 没有），这是为数不多需要应用层配合的场景。

本节记录清单

下一章：JSONB 与半结构化数据——PostgreSQL 的 JSONB 类型让你在关系数据库里存储灵活的 JSON 数据，同时保持可索引、可查询的能力。