第 12.9 课 · 阅读时间：约 7 分钟

行转列（pivot）把某一列的取值变成单独的列：原本是“分类、评级、数量”这样的多行，变成每个分类一行、每种评级一列，就像电子表格中的数据透视表。行转列是大多数交叉报表的基础，“行转列”也是常见的面试题。在本课中，你将用适用于所有数据库的条件聚合来实现行转列，在 MySQL 中构建动态行转列，了解 SQL Server 的 PIVOT 运算符，并把列重新转换为行。

SQL 行转列

GROUP BY category, rating 给出的数字是对的，但形式不方便：80 行“分类—评级”组合。人们更容易阅读矩阵：分类纵向排列，评级横向排列。MySQL、MariaDB、PostgreSQL 和 SQLite 没有 PIVOT 关键字，也不需要它：结果中的每一列都是一个带条件的聚合。这就是第 4.4 课中的条件聚合，这里用它来改变结果的形状。

示例在 Sakila 示例数据库（MySQL 8.0）上运行。

用条件聚合实现行转列

按行标签（分类）分组，并为将来的每一列只统计对应评级的行：

SELECT
    c.name AS category,
    SUM(CASE WHEN f.rating = 'G' THEN 1 ELSE 0 END) AS g,
    SUM(CASE WHEN f.rating = 'PG' THEN 1 ELSE 0 END) AS pg,
    SUM(CASE WHEN f.rating = 'PG-13' THEN 1 ELSE 0 END) AS pg_13,
    SUM(CASE WHEN f.rating = 'R' THEN 1 ELSE 0 END) AS r,
    SUM(CASE WHEN f.rating = 'NC-17' THEN 1 ELSE 0 END) AS nc_17,
    COUNT(*) AS total
FROM film f
JOIN film_category fc ON fc.film_id = f.film_id
JOIN category c ON c.category_id = fc.category_id
GROUP BY c.name
ORDER BY c.name;

结果：16 行，每个分类一行，包含各评级的电影数量和总数。

工作原理：

• GROUP BY c.name 决定透视结果的行。
• 每个 CASE 在电影属于该列时返回 1，否则返回 0，所以 SUM 统计的就是某一评级的电影数量。
• 如果要透视金额而不是数量，就把值放进 CASE：SUM(CASE WHEN ... THEN amount ELSE 0 END)。

在 MySQL 中，比较运算返回 1 或 0，所以 SUM(f.rating = 'G') 是更简短的等价写法。PostgreSQL 提供 COUNT(*) FILTER (WHERE f.rating = 'G')。

按月行转列

一种常见的业务报表：按门店统计收入，每个月一列。值是金额，条件是月份：

SELECT
    s.store_id,
    SUM(CASE WHEN MONTH(p.payment_date) = 5 THEN p.amount ELSE 0 END) AS may_2005,
    SUM(CASE WHEN MONTH(p.payment_date) = 6 THEN p.amount ELSE 0 END) AS jun_2005,
    SUM(CASE WHEN MONTH(p.payment_date) = 7 THEN p.amount ELSE 0 END) AS jul_2005,
    SUM(CASE WHEN MONTH(p.payment_date) = 8 THEN p.amount ELSE 0 END) AS aug_2005
FROM payment p
JOIN staff s ON s.staff_id = p.staff_id
WHERE p.payment_date >= '2005-01-01'
  AND p.payment_date < '2006-01-01'
GROUP BY s.store_id
ORDER BY s.store_id;

结果：2 行；例如，门店 1 在 2005 年 5 月收入 2621.83，7 月收入 14003.54。

WHERE 条件把数据限制在一年之内，因为仅用 MONTH() 会把 2005 年 7 月和 2006 年 7 月混在一起。当空单元格应显示 0 时请使用 ELSE 0；没有 ELSE 的话，SUM 会对它返回 NULL。

MySQL 中的动态行转列

查询的所有列在执行前都必须是已知的。如果需要转成列的取值会变化（新的评级、新的月份），就必须动态生成查询。在 MySQL 中，用 GROUP_CONCAT 拼出列列表，再把文本作为预处理语句执行：

SET @columns = (
    SELECT GROUP_CONCAT(DISTINCT
        CONCAT('SUM(f.rating = ''', rating, ''') AS `', rating, '`')
        ORDER BY rating)
    FROM film
);

SET @sql = CONCAT(
    'SELECT c.name AS category, ', @columns, '
     FROM film f
     JOIN film_category fc ON fc.film_id = f.film_id
     JOIN category c ON c.category_id = fc.category_id
     GROUP BY c.name
     ORDER BY c.name');

PREPARE pivot_stmt FROM @sql;
EXECUTE pivot_stmt;
DEALLOCATE PREPARE pivot_stmt;

结果：同样的 16 个分类，每种评级一列；这些列是根据数据生成的。

有两点需要注意：这些值会被拼接进 SQL 文本，所以只能用于来自你自己表中的值，绝不能用于用户输入；另外，很长的列列表可能需要调大 group_concat_max_len（默认 1024 个字符）。很多时候，更简单的做法是返回 GROUP BY 的长格式结果，再在应用程序或 BI 工具中做行转列。

SQL Server 和 Oracle 中的 PIVOT 运算符

SQL Server 和 Oracle 有专门的语法。同样的报表在 SQL Server 中写作：

SELECT category, [G], [PG], [PG-13], [R], [NC-17]
FROM (
    SELECT c.name AS category, f.rating, f.film_id
    FROM film f
    JOIN film_category fc ON fc.film_id = f.film_id
    JOIN category c ON c.category_id = fc.category_id
) AS src
PIVOT (
    COUNT(film_id) FOR rating IN ([G], [PG], [PG-13], [R], [NC-17])
) AS pvt;

IN (...) 中的值仍然需要事先列出。条件聚合能实现同样的效果，而且到处都能用，所以在面试中它是更稳妥的答案。

列转行（Unpivot）

当数据以电子表格的形式到来（每个月一列）时，就会遇到相反的任务。每一列写一个 SELECT，再用 UNION ALL 合并，就能把列转换回行：

WITH store_months AS (
    SELECT
        s.store_id,
        SUM(CASE WHEN MONTH(p.payment_date) = 7 THEN p.amount ELSE 0 END) AS jul_2005,
        SUM(CASE WHEN MONTH(p.payment_date) = 8 THEN p.amount ELSE 0 END) AS aug_2005
    FROM payment p
    JOIN staff s ON s.staff_id = p.staff_id
    WHERE p.payment_date >= '2005-07-01'
      AND p.payment_date < '2005-09-01'
    GROUP BY s.store_id
)
SELECT store_id, '2005-07' AS month, jul_2005 AS amount FROM store_months
UNION ALL
SELECT store_id, '2005-08', aug_2005 FROM store_months
ORDER BY store_id, month;

结果：4 行，每家门店每个月一行，例如门店 1 – 2005-07 – 14003.54。

SQL Server 和 Oracle 也有 UNPIVOT 运算符；PostgreSQL 可以使用 CROSS JOIN LATERAL (VALUES ...)。

练习

• 人口分布（行转列）
• 按分类和门店统计电影分布
• 按月份和支付方式统计收入

要点：

• 行转列把某一列的取值变成单独的列。
• GROUP BY 决定行；SUM(CASE WHEN column = value THEN ... ELSE 0 END) 生成每一列。这种写法适用于所有数据库。
• 统计金额时在 THEN 中放值；统计数量时放 1。
• 当列事先未知时，就动态生成查询：在 MySQL 中用 GROUP_CONCAT 和 PREPARE。
• SQL Server 和 Oracle 有 PIVOT 和 UNPIVOT；UNION ALL 在任何数据库中都能实现列转行。

常见问题

如何在 MySQL 中把行转成列？

MySQL 没有 PIVOT 运算符。请使用条件聚合：按行标签 GROUP BY，并为每个输出列写一个 SUM(CASE WHEN pivot_column = 'value' THEN amount ELSE 0 END) AS value。

如何实现列动态变化的行转列？

动态生成 SQL 文本：用 GROUP_CONCAT 收集不同的取值，据此拼出 SUM(CASE ...) 列表，然后用 PREPARE 和 EXECUTE 执行。在 SQL Server 中，可以用 STRING_AGG 和 sp_executesql 实现同样的做法。只使用来自你自己表中的可信值。

PIVOT 和条件聚合有什么区别？

两者结果相同。PIVOT 是更简短的语法，但只有 SQL Server 和 Oracle 支持。条件聚合适用于所有数据库，并且允许每一列使用不同的条件和聚合函数。

如何把列转换成行（unpivot）？

每一列写一个 SELECT，分别返回键、作为字面量的列名以及该列的值，然后用 UNION ALL 合并。SQL Server 和 Oracle 也提供 UNPIVOT。

→ Sakila 数据库上的更多题目