SQL 如何用 PIVOT / UNPIVOT 实现行列转换（跨数据库）

sql Server中PIVOT必须配合子查询或CTE使用且不可省略聚合函数；postgresql/mysql需用CASE WHEN+SUM/MAX模拟；UNPIVOT跨库需union ALL显式枚举；原生语法缺乏动态性与兼容性。

PIVOT 在 SQL Server 中怎么写才不报错

SQL Server 的 PIVOT 是语法糖，不是函数，必须配合子查询或 CTE 使用，且聚合函数不可省略。常见错误是直接对表名用 PIVOT，或者漏写 for ... IN (...) 中的列值列表。

典型结构：

select * FROM (   SELECT category, amount, year   FROM sales ) AS src PIVOT (   SUM(amount) FOR year IN ([2021], [2022], [2023]) ) AS pvt;

• src 子查询必须提供至少三列：要聚合的值（amount）、分组依据（如 category）、要转成列头的字段（year）
• IN 括号里的值必须是**字面量**，不能是子查询或变量；动态列需拼接 SQL 字符串执行
• 如果 year 值含空格或特殊字符，要用方括号包裹，如 [Q1 Sales]
• 未出现在 IN 中的 year 值会被直接丢弃，不会报错但数据会丢失

PostgreSQL 和 MySQL 根本没有 PIVOT 关键字怎么办

PostgreSQL 和 MySQL 不支持原生 PIVOT，得用条件聚合模拟，核心是 CASE WHEN + MAX/SUM 组合。这不是“替代方案”，而是跨数据库最通用、最可控的做法。

等效写法（PostgreSQL / MySQL / sqlite / oracle 都适用）：

SELECT   category,   SUM(CASE WHEN year = 2021 THEN amount END) AS "2021",   SUM(CASE WHEN year = 2022 THEN amount END) AS "2022",   SUM(CASE WHEN year = 2023 THEN amount END) AS "2023" FROM sales GROUP BY category;

• 每个目标列对应一个 CASE WHEN 表达式，聚合函数不能用 NULL 安全的 COALESCE 替代——因为 CASE 本身在不匹配时就返回 NULL，而聚合会自动跳过 NULL
• MySQL 8.0+ 支持 jsON_OBJECTAGG 做动态行转列，但结果是 json 字段，不是宽表，实用性受限
• PostgreSQL 可用 crosstab() 函数（来自 tablefunc 扩展），但它要求输入严格排序，且列定义需提前声明，灵活性反而不如手工 CASE

UNPIVOT 的跨库实现比 PIVOT 还麻烦

SQL Server 有 UNPIVOT，但 PostgreSQL/MySQL 没有对应语法，且反向转换（宽表 → 长表）容易漏数据或重复。关键问题在于：源列名要变成结果中的值，而标准 SQL 没有“列名反射”能力。

安全做法是显式枚举每一列，用 UNION ALL 拼接：

SELECT category, '2021' AS year, "2021" AS amount FROM sales_wide UNION ALL SELECT category, '2022', "2022" FROM sales_wide UNION ALL SELECT category, '2023', "2023" FROM sales_wide;

• 每条 SELECT 必须字段数、类型一致；字符串字面量要用单引号，列别名用双引号（PostgreSQL）或反引号（MySQL）
• 避免用 UNION（带去重）——性能差且可能误删合法重复行，一律用 UNION ALL
• 如果原始宽表有上百列，手写不现实，得靠应用层生成 SQL 或用元数据查 information_schema.columns 动态构造
• 注意 NULL 处理：某些数据库（如 older MySQL）在 UNION ALL 中对 NULL 类型推断不准，建议显式 CAST(... AS DECIMAL)

为什么别依赖数据库自带 PIVOT / UNPIVOT

除了 SQL Server，其他主流数据库要么没实现，要么实现方式差异大（如 Oracle 的 PIVOT xml 返回 XMLType）。更实际的问题是：业务中行列转换往往需要动态列、过滤条件嵌套、或与其他窗口函数混用，原生语法很快就不够用。

• SQL Server 的 PIVOT 不支持在 IN 里用变量，动态列必须拼 SQL + EXEC，引入注入和缓存失效风险
• 所有数据库的行列转换都会放大中间结果集内存占用，尤其当分组键基数高、列数多时，容易触发临时表磁盘溢出
• 应用层做转换（如 python pandas pivot_table 或 java stream.collect）有时更灵活，特别是需要补零、插值、或带业务逻辑的聚合时

真正难的不是写出第一版 PIVOT，而是让转换逻辑能随新增年份、品类、指标自动适配，而这恰恰是原生语法最薄弱的地方。