SQL 数据分析 · 指南 · 2026年10月30日 · 13 分钟阅读

SQL 行转列与列转行：PIVOT、UNPIVOT 和 CASE WHEN 写法实例

SQL 行转列（pivot），就是把某一列的不同取值变成单独的列，每个值一列，效果和 Excel 的数据透视表一样。通用的写法是条件聚合：按行标签 GROUP BY，每个输出列写一个聚合函数，各自只计算或累加属于自己那个值的行，比如 SUM(CASE WHEN month = 7 THEN 1 ELSE 0 END) AS jul。这种写法在所有数据库里都能运行。有些引擎提供了替你生成这些列的 PIVOT 运算符（DuckDB、SQL Server、Oracle、Snowflake、BigQuery、Spark SQL）；PostgreSQL 用 crosstab 函数代替；MySQL 和 Hive 都没有。反过来的操作叫列转行（unpivot），把列重新变回行。

下面的例子都在 Nomto SQL 课程的航空公司数据库上运行，使用 PostgreSQL 语法，引擎是 DuckDB（Nomto SQL 练习场背后的引擎），每个结果表都是真实输出。SQL Server 和 PostgreSQL crosstab 的代码片段都做了标注，没有在这里运行。

长表和宽表

对两列做 GROUP BY，得到的是长表：每种组合一行。按机型和月份统计航班数：

SELECT a.model,
       date_trunc('month', f.scheduled_departure)::date AS month,
       COUNT(*) AS flights
FROM flights f
JOIN aircrafts a ON a.aircraft_code = f.aircraft_code
GROUP BY a.model, month
ORDER BY a.model, month
LIMIT 6;

这是 24 行里的前六行。数据库、图表和后续的 SQL 都更喜欢长表。但看报表的人通常想要宽表：每个机型一行，每个月一列。从长表变成宽表，就是行转列。

用 CASE WHEN 行转列：通用写法

只按行标签分组，每一列用一个只看某个月份的聚合函数来构造：

SELECT a.model,
       SUM(CASE WHEN EXTRACT(month FROM f.scheduled_departure) = 7 THEN 1 ELSE 0 END) AS jul,
       SUM(CASE WHEN EXTRACT(month FROM f.scheduled_departure) = 8 THEN 1 ELSE 0 END) AS aug,
       SUM(CASE WHEN EXTRACT(month FROM f.scheduled_departure) = 9 THEN 1 ELSE 0 END) AS sep,
       COUNT(*) AS total
FROM flights f
JOIN aircrafts a ON a.aircraft_code = f.aircraft_code
GROUP BY a.model
ORDER BY total DESC;

（航班时刻表从 7 月 12 日到 9 月 10 日，所以 7 月和 9 月都不是完整的月份。）

原理：每一行航班数据都会经过三个 CASE 表达式，每个表达式只在这一行属于自己的月份时加 1。然后 GROUP BY a.model 把这些行压缩成每个机型一行。行合计顺手就有：在同一个 SELECT 里写 COUNT(*)。第九种机型 Airbus A320-200 一个航班都没有，所以内连接把它排除了；如果空行也要显示，就从 aircrafts 开始，用 LEFT JOIN。

这种写法在 PostgreSQL、MySQL、SQL Server、Oracle、SQLite、Hive、Spark SQL 和 DuckDB 里都能原样运行（只是取月份的函数不同：MySQL、SQL Server 和 Hive 用 MONTH()）。要累加金额而不是计数，就在 THEN 分支里返回这个值：SUM(CASE WHEN ... THEN amount ELSE 0 END)。MySQL 和 Hive 里也常写成更短的 SUM(IF(..., 1, 0))。SUM 和 COUNT 的细节区别见 SQL CASE WHEN 用法。

FILTER：同样的行转列，更简短

PostgreSQL、DuckDB 和 SQLite 支持标准的 FILTER 子句，列很多时读起来更清楚。行转列把相关的数字并排放在一起，所以下一步就可以在新列之间计算比率。按平台统计 App 事件和转化率：

WITH p AS (
  SELECT platform,
         COUNT(*) FILTER (WHERE event_name = 'search')         AS searches,
         COUNT(*) FILTER (WHERE event_name = 'checkout_start') AS checkouts,
         COUNT(*) FILTER (WHERE event_name = 'purchase')       AS purchases
  FROM app_events
  GROUP BY platform
)
SELECT platform, searches, checkouts, purchases,
       ROUND(100.0 * purchases / searches, 1)  AS purchases_per_100_searches,
       ROUND(100.0 * purchases / checkouts, 1) AS checkout_to_purchase_pct
FROM p
ORDER BY platform;

网页端在两个步骤上的转化都更好。如果是长表，同样的比较需要自连接或窗口函数；行转列之后，就是列与列之间的简单算术。（这些是原始事件数；表里还包含重复发送的事件，去掉它们的方法见 SQL 查找重复数据。）

MySQL、SQL Server、Oracle 和 Hive 没有 FILTER，在那里用 SUM(CASE ...)。

转换值而不是计数：MAX(CASE …)

当每个单元格恰好只有一个值时，比如以行形式存储的属性，用 MAX（或 MIN）把它取出来。谢列梅捷沃出发航线的当前票价，每个舱位一列：

SELECT departure_airport, arrival_airport,
       MAX(CASE WHEN fare_conditions = 'Economy'  THEN price END) AS economy,
       MAX(CASE WHEN fare_conditions = 'Comfort'  THEN price END) AS comfort,
       MAX(CASE WHEN fare_conditions = 'Business' THEN price END) AS business
FROM fare_price_history
WHERE valid_to IS NULL
  AND departure_airport = 'SVO'
GROUP BY departure_airport, arrival_airport
ORDER BY business DESC NULLS LAST
LIMIT 5;

这里的 CASE 没有 ELSE，所以缺少的舱位显示为 NULL，这是正确的：这些航线没有 Comfort 票价（整张表里只有 10 条航线有），写成价格 0 就是在撒谎。计数则正好相反：「没有航班」的正确答案是 0，用 ELSE 0 或 COUNT 就能得到。MAX 对文本和日期同样适用，所以这是把键值对行（属性名、属性值）转成列的标准做法。

DuckDB 的 PIVOT 语句

DuckDB 有 PIVOT 语句。ON 指定哪一列的值要变成列，USING 指定聚合函数，GROUP BY 指定行标签。用明确的 IN 列表，可以自己选择要哪些列以及列名：

PIVOT (
  SELECT a.model, EXTRACT(month FROM f.scheduled_departure) AS m
  FROM flights f
  JOIN aircrafts a USING (aircraft_code)
)
ON m IN (7 AS jul, 8 AS aug, 9 AS sep)
USING COUNT(*)
GROUP BY model
ORDER BY model;

数字和 CASE 版本一样。DuckDB 的 PIVOT 还有两个方便的地方。一是可以同时用多个聚合函数，每个聚合函数对每个值各生成一列：

PIVOT (
  SELECT f.departure_airport, tf.fare_conditions, tf.amount
  FROM ticket_flights tf
  JOIN flights f USING (flight_id)
  WHERE f.departure_airport IN ('SVO', 'DME', 'VKO')
)
ON fare_conditions IN ('Economy', 'Business')
USING COUNT(*) AS seg, ROUND(SUM(amount)) AS rev
GROUP BY departure_airport
ORDER BY departure_airport;

二是不写 IN 时，它会从数据里自己找出有哪些列：

PIVOT flights
ON status
USING COUNT(*)
GROUP BY departure_airport
ORDER BY departure_airport
LIMIT 3;

探索数据时很方便，但注意列名 On Time 里有空格，而且列的集合现在取决于数据。下文会再讲这一点。

SQL Server、Oracle 和 Spark SQL 的 PIVOT

SQL Server 的 PIVOT 是写在 FROM 里的一个子句。没有在这里运行，结构如下：

-- SQL Server
SELECT model, [7] AS jul, [8] AS aug, [9] AS sep
FROM (
  SELECT a.model, MONTH(f.scheduled_departure) AS m, f.flight_id
  FROM flights f
  JOIN aircrafts a ON a.aircraft_code = f.aircraft_code
) AS src
PIVOT (
  COUNT(flight_id) FOR m IN ([7], [8], [9])
) AS p;

陷阱在于：这里没有 GROUP BY。SQL Server 会按源数据中所有没有参与 pivot 的列分组。所以源数据要写成只有 model、m 和 flight_id 的子查询；如果在那里选了 f.*，得到的就是每个航班一行，而不是每个机型一行。IN 列表里的列名用方括号，而且只允许一个聚合函数。Oracle 的 PIVOT 看起来差不多，但允许多个聚合函数，IN 里也可以写带引号的字面量。

Spark SQL（2.4 起）也有类似的 PIVOT 子句，写法接近 Oracle：... PIVOT (COUNT(*) FOR m IN (7 AS jul, 8 AS aug, 9 AS sep))，同样按其余所有列隐式分组。Hive 没有 PIVOT，在 Hive 里只能用 SUM(CASE ...) 或 SUM(IF(...))。

PostgreSQL：crosstab

PostgreSQL 没有 PIVOT。tablefunc 扩展提供了 crosstab 函数，它接收一个返回（行标签、类别、值）的查询，再把它展开。没有在这里运行：

-- PostgreSQL，需要：CREATE EXTENSION tablefunc;
SELECT *
FROM crosstab(
  $$SELECT a.model::text, EXTRACT(month FROM f.scheduled_departure)::int, COUNT(*)
    FROM flights f
    JOIN aircrafts a ON a.aircraft_code = f.aircraft_code
    GROUP BY 1, 2
    ORDER BY 1, 2$$,
  $$VALUES (7), (8), (9)$$
) AS ct(model text, jul bigint, aug bigint, sep bigint);

你仍然需要在 AS ct(...) 里列出输出列和它们的类型，源查询必须按行标签排序，空单元格返回的是 NULL 而不是 0。务必使用这里展示的双参数形式：单参数版本按从左到右的顺序填列，某一行缺少某个类别时，值会错位到别的列里。大多数 PostgreSQL 用户觉得 COUNT(*) FILTER (WHERE ...) 更简单，也就一直用它。

动态行转列：事先不知道有哪些列

SQL 查询在运行之前就必须确定输出列。这就是为什么上面每种方法都要手动列出值（jul、aug、sep），也是为什么在大多数数据库里，「不管有哪些不同的值，全部转成列」没法用一条普通查询完成。可选的办法：

• 列出所有值。 最适合报表：列是稳定的、有名字的、顺序合理。如果数据里出现了新的状态，它会被悄悄漏掉，所以在需要时加一个 other 列（COUNT(*) FILTER (WHERE status NOT IN (...))）。
• 生成 SQL。 在 SQL Server 里用 STRING_AGG 从数据中拼出列清单，再用 sp_executesql 执行；在 PostgreSQL 里写一个带 EXECUTE 的函数；在 MySQL 里用 GROUP_CONCAT 拼出语句再用预处理语句执行；或者在 Python 或 BI 工具里拼查询。
• 让引擎来做。 DuckDB 不带 IN 的 PIVOT，Snowflake 的 PIVOT ... ANY。很方便，但结果的结构会随数据变化，会让下游期望固定列的代码出错。
• 根本不在 SQL 里做行转列。 返回长表，在 Excel 数据透视表、BI 工具（Tableau、Power BI、帆软 FineBI 等）或 pandas（pivot_table）里转换。这往往是最清晰的分工。

列转行：把列变回行

列转行把宽表变成长表：每个（行、列）对变成一行，带一个「名称」列和一个「值」列。数据是每月一列的表格文件时，或者要给宽表画图时，就需要它。先构造一张每个舱位一列的座位宽表，再在 DuckDB 里列转行：

WITH wide AS (
  SELECT aircraft_code,
         COUNT(*) FILTER (WHERE fare_conditions = 'Economy')  AS economy,
         COUNT(*) FILTER (WHERE fare_conditions = 'Comfort')  AS comfort,
         COUNT(*) FILTER (WHERE fare_conditions = 'Business') AS business
  FROM seats
  GROUP BY aircraft_code
)
UNPIVOT wide
ON economy, comfort, business
INTO NAME fare_class VALUE seats
ORDER BY aircraft_code, fare_class
LIMIT 6;

通用的替代写法在任何数据库里都能用：每一列写一个 SELECT，用 UNION ALL 拼起来。

SELECT aircraft_code, 'economy' AS fare_class, economy AS seats FROM wide
UNION ALL
SELECT aircraft_code, 'comfort', comfort FROM wide
UNION ALL
SELECT aircraft_code, 'business', business FROM wide;

这种写法每一列都要读一遍表。PostgreSQL 和 DuckDB 可以用 LATERAL 连接一个 VALUES 列表，一遍搞定（SQL Server 里同样的思路写成 CROSS APPLY (VALUES ...)）：

SELECT w.aircraft_code, v.fare_class, v.seats
FROM wide w
CROSS JOIN LATERAL (
  VALUES ('economy', w.economy), ('comfort', w.comfort), ('business', w.business)
) AS v(fare_class, seats)
WHERE w.aircraft_code = '773'
ORDER BY v.seats DESC;

（两段都和上面一样，以 WITH wide AS (...) 开头运行。）SQL Server 也有 UNPIVOT 子句：FROM wide UNPIVOT (seats FOR fare_class IN (economy, comfort, business)) AS u。在 Hive 和 Spark SQL 里，列转行常用 stack() 函数：SELECT aircraft_code, fare_class, seats FROM wide LATERAL VIEW stack(3, 'economy', economy, 'comfort', comfort, 'business', business) t AS fare_class, seats（Spark SQL 3.4 起还有 UNPIVOT 子句）。

有一个区别要注意：DuckDB 和 SQL Server 的 UNPIVOT 会丢掉为 NULL 的单元格，所以没有 Comfort 票价的航线就干脆没有 Comfort 这一行。UNION ALL 和 LATERAL VALUES 会保留这些行，值为 NULL。下一步需要哪一种，要想清楚。

该用哪种方法

常见问题

SQL 怎么实现行转列？

按要保留为行的列分组，每个新列写一个只针对某个值的聚合函数：SUM(CASE WHEN category = 'A' THEN amount ELSE 0 END) AS a。这种条件聚合在所有 SQL 数据库里都能用。DuckDB、SQL Server、Oracle 和 Spark SQL 还提供 PIVOT 运算符。

PostgreSQL 有 PIVOT 吗？

没有。用 FILTER (WHERE ...) 或 CASE 做条件聚合，或者用 tablefunc 扩展里的 crosstab 函数。

MySQL 有 PIVOT 吗？

没有。MySQL 用 SUM(CASE ...) 或 SUM(IF(...)) 配合 GROUP BY 做行转列。如果列清单取决于数据，就用 GROUP_CONCAT 拼出查询，再作为预处理语句执行。

Hive SQL 怎么行转列和列转行？

Hive 没有 PIVOT 和 UNPIVOT。行转列用 SUM(CASE ...) 或 MAX(IF(...)) 配合 GROUP BY；列转行用 stack() 或 LATERAL VIEW explode(map(...))。

事先不知道列名，能行转列吗？

在大多数数据库里，一条静态查询做不到，因为输出列必须在查询运行之前确定。可以用动态 SQL、DuckDB 不带 IN 列表的 PIVOT，或者返回长表，在 BI 工具或 Excel 数据透视表里转换。

PIVOT 和 GROUP BY 有什么区别？

GROUP BY 对分组值的每种组合返回一行（长表）。行转列在此基础上把其中一个分组列横向展开成多个单独的列（宽表）。本质上，行转列就是一个 GROUP BY，外加每个输出列一个条件聚合。

其他语言： English, Español, Português (Brasil), Tiếng Việt