简介：围绕 SQL Server 中行转列 PIVOT 操作符的实战讲解，面向数据库开发、报表制作及数据分析人员，解决将行数据转为列展示的常见需求，尤其适合需要快速生成横向周报/月报的读者。内容从店铺一周收入表（WEEK_INCOME）出发，先展示传统 CASE WHEN + SUM 的写法，再引入 SQL Server 2005 以上版本的 PIVOT 语法，讲解其三个关键步骤：准备原始结果集、执行聚合转换、选择输出列。同时说明了 PIVOT 中聚合函数、FOR 子句与 IN 值列表的作用，补充了子查询需要别名等易错细节，并指出当列名动态变化或数据量极大时 PIVOT 的局限及应对思路。资源为 1 个 PDF 文件，大小 66KB，内容紧凑且含完整示例 SQL，可随查随用。已有 1529 人学习下载，适合初级至中级开发者对照练习并迁移到实际报表场景。
1. 行转列到底在解决什么问题
做业务报表时，最常碰到的一类需求是“把一列里的多个分类值拆成多个列”：月份变成一月、二月、三月，产品名称变成产品A、产品B、产品C。数据表里看起来没问题的明细，落到 Excel 横向对比时就要写一堆 SUM(CASE WHEN ...) 手动凑列，列一多，SQL 几十行，维护全靠改字符串。SQL Server 从 2005 版本开始提供 PIVOT 关键字，专门把这种“按某个字段的值横向展开”的聚合逻辑收进一段语法里，2008 R2 到 2022 的各版本写法一直保持一致。这篇文章就是把静态 PIVOT、动态列 PIVOT、UNPIVOT 反透视、性能与索引调优串起来讲，覆盖从理解原理到写进存储过程的完整路径。
2. 静态 PIVOT：行转列的基础语法与聚合选型
2.1 标准语法拆解：源数据、聚合、FOR 列
先准备一张典型的销售明细表：产品分类、年份、销量三个字段。用 PIVOT 把年份转成列，最基础的一段 SQL 长这样：
SELECT 产品分类, [2023] AS 销量2023, [2024] AS 销量2024 FROM ( SELECT 产品分类, 年份, 销量 FROM dbo.销售明细 WHERE 年份 IN (2023, 2024) ) AS 源数据 PIVOT ( SUM(销量) FOR 年份 IN ([2023], [2024]) ) AS 透视表;这段代码做了三件事：FROM 子查询不聚合，只负责挑选所需的行并准备好列；PIVOT 块里的 SUM(销量) 是对每一列单元格的聚合方式；FOR 年份 IN ([2023], [2024]) 把年份字段的取值拆成两个输出列。关键字 PIVOT 后面括号里的写法，中文文档常叫透视列，IN 后面的值必须写成常量并加方括号，写成 [2023] 表示一个标识符。如果写成 PIVOT (SUM(销量) FOR 年份 IN (2023))，数字会被当成常量而不是列名，直接报语法错误。
版本兼容性在这里值得单独说一句：SQL Server 2008 R2、2012、2016、2019、2022 里，这段语法的行为完全一致，SSMS 里新建查询就能直接跑。如果用的是 2019 或 2022，注意数据库兼容级别低于 100 时可能触发旧版基数估计，结果集不受影响，但执行计划的选择会变化，这个在第 4 章再展开。
2.2 聚合选型：SUM、COUNT、MAX 分别什么时候用
PIVOT 里的聚合函数不是随便填的，它决定透视出来的单元格语义。下面这个表是我在写报表时最常用的选型：
| 聚合函数 | 适用场景 | 透视结果含义 | 备注 |
|---|---|---|---|
| SUM | 金额、数量、时长 | 分组内的合计值 | 最常用，适合度量值累加 |
| COUNT | 工单数、拜访次数 | 分组内的记录条数 | 注意 COUNT 不统计 NULL |
| MAX/MIN | 最新状态、最大库存 | 分组内的极值 | 也常用于“该组合是否存在”判断 |
只看表格容易踩业务口径的坑。比如当月未成交的省份，在透视结果里是 NULL 而不是 0，如果报表端要求显示 0，必须在外层用 ISNULL([2023], 0) 包一层。NULL 和 0 在后续聚合里的行为完全不同：SUM 遇到 NULL 会忽略，COUNT 遇到 NULL 不计入行数，MAX 遇到 NULL 返回非 NULL 值，这些差异在透视表里会被成倍放大。所以写 PIVOT 前先确认“缺失值在业务里到底代表不存在还是 0”，这句话在团队协作里能省掉不少沟通成本。
2.3 用 GROUP BY + CASE WHEN 验证 PIVOT 的等价逻辑
PIVOT 写完先别急着上线，最靠谱的验证方法是用“传统写法”对拍。下面这个查询和上一节的 PIVOT 逻辑等价：
SELECT 产品分类, SUM(CASE WHEN 年份 = 2023 THEN 销量 ELSE 0 END) AS 销量2023, SUM(CASE WHEN 年份 = 2024 THEN 销量 ELSE 0 END) AS 销量2024 FROM dbo.销售明细 WHERE 年份 IN (2023, 2024) GROUP BY 产品分类;这个写法里，每一列是由一个 SUM 包裹的 CASE WHEN 生成的，PIVOT 只是把这种模式转成声明式语法。SQL Server 优化器遇到简单聚合的 PIVOT 时，通常会把执行计划也转换成类似的分组聚合，不会因为用了 PIVOT 就多一次排序或哈希。验证步骤：跑一遍 PIVOT 和 GROUP BY 两个查询，对比行数、列数，以及同一列汇总值的加总是否一致，全对再替换旧存储过程。
这里有一个容易踩的坑：如果同一产品分类下有同年多条记录，SUM 会全部累加；如果业务只要“当年是否存在”，SUM 结果可能让你误以为值很大，这时选 MAX 或 COUNT 更合适。PIVOT 语法本身不负责去重，去重逻辑要放在源数据子查询里先做掉。
3. 动态列 PIVOT：列不确定时的构建方案
3.1 QUOTENAME 与 FOR XML PATH 生成透视列清单
静态 PIVOT 的列写在 SQL 里，但真实报表往往是“今年有 2023、2024、2025，明年再多一个 2026”，每加一列就改一次脚本，维护成本很高。常见做法是先用一个查询把透视列的值拼成字符串，再把它嵌进动态 SQL 执行，SQL Server 2008 到 2022 都能用的拼接方式是 FOR XML PATH 加 STUFF：
DECLARE @columns NVARCHAR(MAX); SELECT @columns = STUFF( ( SELECT ',' + QUOTENAME(年份) FROM ( SELECT DISTINCT 年份 FROM dbo.销售明细 WHERE 年份 IN (2023, 2024, 2025) ) AS 年份表 ORDER BY 年份 FOR XML PATH(''), TYPE ).value('.', 'NVARCHAR(MAX)'), 1, 1, '' ); SELECT @columns AS 列清单;这段代码里值得拆开讲的有四个点。QUOTENAME(年份) 给每个值加方括号，[2023] 是合法列标识符，不套的话字符串拼出来是 2023,2024,2025 这种裸数字，拼进 SQL 后很可能被当成常量处理。ORDER BY 年份决定透视列从左到右的顺序，这直接影响最终图表展示，写生产脚本时绝对不能省。STUFF(..., 1, 1, '') 把拼接结果里第一个逗号删掉，因为每个值前面都加了逗号。最后的 TYPE 参数防止 FOR XML PATH 把<、&这类字符转义掉，年份场景不踩，但如果透视值是英文产品名就要特别当心。
为什么不用 SQL Server 2017 才有的 STRING_AGG：2022 当然能跑，但很多存量系统还在 2016 甚至 2008 R2，FOR XML PATH 是版本跨度最大、行为最一致的写法，生产环境里普遍沿用这个方案。
3.2 动态 SQL 拼接与 sp_executesql 执行
拿到列清单字符串后，完整语句就只剩拼接这一步：
DECLARE @sql NVARCHAR(MAX); SET @sql = N' SELECT 产品分类, ' + @columns + N' FROM ( SELECT 产品分类, 年份, 销量 FROM dbo.销售明细 WHERE 年份 IN (2023, 2024, 2025) ) AS 源数据 PIVOT ( SUM(销量) FOR 年份 IN (' + @columns + N') ) AS 透视表 ORDER BY 产品分类;'; EXEC sp_executesql @sql;说明几点。N'' 前缀必须带，动态 SQL 里有中文表名字段名时，不写 N 前缀可能遇到字符串编码问题，中文 Windows 下的 SQL Server 尤其容易出现。sp_executesql 比 EXEC 更适合推荐，一是支持参数化输入，二是执行计划能按同一模板复用，三是 SQL 文本较长时通过 @params 参数传递，比粗暴拼接更安全。@columns 来源于数据库里的 DISTINCT 值，一般没有注入风险，如果未来业务允许外部系统写入年份或其他分类字符串，动态列名来源就必须加白名单校验，只允许字母、数字和下划线。
sp_executesql 与 EXEC 的对比：
| 对比项 | EXEC | sp_executesql |
|---|---|---|
| 参数化 | 不支持 | 支持 @params 定义 |
| 计划复用 | 每次重新编译 | 同模板可复用 |
| 拼接风险 | 相对更高 | 可通过参数控制 |
| 常用场景 | 一次性的简单语句 | 存储过程里的动态 SQL、高频执行 |
3.3 动态 PIVOT 结果落临时表（INSERT INTO … EXEC）
动态 SQL 的结果没办法被后面的静态查询直接引用，报表系统通常要求“动态 PIVOT 生成一张临时表，之后继续查”。落临时表的典型写法是 INSERT INTO ... EXEC：
IF OBJECT_ID('tempdb..#pivot_result') IS NOT NULL DROP TABLE #pivot_result; CREATE TABLE #pivot_result ( 产品分类 NVARCHAR(50), 销量2023 DECIMAL(18, 2), 销量2024 DECIMAL(18, 2), 销量2025 DECIMAL(18, 2) ); INSERT INTO #pivot_result EXEC sp_executesql @sql; SELECT 产品分类, 销量2023, 销量2024, 销量2025 FROM #pivot_result;这里有个必须接受的事实：临时表列必须静态声明，所以一旦透视列集合变化，CREATE TABLE 的列定义也要跟着变。常见做法是每次执行前先查一把当前有哪些列，再用动态语句同时生成列定义和 INSERT 语句，把整段“建表+插入”一起拼进动态 SQL 执行。这种做法的代价是临时表结构对静态引用不透明，本质是把维护成本从“改脚本”转移到了“改拼接逻辑”。如果 BI 平台固定用 Power BI 或 SSRS，也可以让 PIVOT 的结果直接作为数据集，跳过临时表这层。
4. PIVOT 性能权衡与索引设计要点
4.1 对比执行计划：PIVOT 与 GROUP BY 是否真的更慢
“PIVOT 慢”是常见误解。PIVOT 只是语法糖，SQL Server 优化器看到简单聚合的 PIVOT，会把计划转换成 Stream Aggregate 或 Hash Match；同一份数据下，和手写 GROUP BY + CASE WHEN 的计划基本一致。可以打开 SSMS 按 Ctrl+M 开启实际执行计划，把第 2 章的两种写法逐条跑一遍，对比两件事：是否出现相同的聚合运算符、估计行数是否一致，两者几乎相同。
真正容易拖慢 PIVOT 的是两个地方。第一个是源数据子查询选列太宽，SELECT * 把不需要的文本字段全部带进内存，聚合前排序的量变大；第二个是透视列数量多到上百，拼接出来的 SQL 文本上万字符，编译阶段耗时上升，这在动态列 PIVOT 场景里尤其常见。解决办法：先过滤，只保留分组列、透视列和聚合值列三个字段。
4.2 透视场景下复合索引的写法
PIVOT 执行快慢，取决于第一次扫描能不能把数据压缩到最小。对常见的透视逻辑，索引设计的最优顺序是“等值过滤列在前、分组列在后、聚合值列放 INCLUDE”：
| 索引列顺序 | 适用场景 | 说明 |
|---|---|---|
| (年份, 产品分类) INCLUDE(销量) | 先按年份过滤再聚合 | 年份做点查，索引覆盖剩余操作 |
| (产品分类, 年份) INCLUDE(销量) | 先按分类横向展开 | 避免按分类分组后回表取销量 |
对应的创建语句：
CREATE NONCLUSTERED INDEX IX_销售明细_年份_分类 ON dbo.销售明细 (年份, 产品分类) INCLUDE (销量);为什么这样建：年份在 PIVOT 里是 FOR 列，条件通常固定为最近 N 年，SQL Server 可以把它当点查；产品分类是分组列，把这两个字段放索引最前面，能覆盖整段查询的过滤和分组。INCLUDE 里的销量是为了避免查询到销量字段时回表。如果透视的是月度明细，季度、月份这类字段有大量重复值，还可以进一步考虑压缩索引或分区表，但那属于另一套方案，普通报表场景先建好这个复合索引就够了。
4.3 用 SET STATISTICS IO 观察透视开销
性能调优不要靠感觉，SQL Server 本身就带测量工具：
SET STATISTICS IO ON; SET STATISTICS TIME ON; SELECT 产品分类, [2023] AS 销量2023, [2024] AS 销量2024 FROM ( SELECT 产品分类, 年份, 销量 FROM dbo.销售明细 WHERE 年份 IN (2023, 2024) ) AS 源数据 PIVOT ( SUM(销量) FOR 年份 IN ([2023], [2024]) ) AS 透视表; SET STATISTICS IO OFF; SET STATISTICS TIME OFF;执行完后看消息选项卡里的表扫描计数、逻辑读取、CPU 时间和经过时间。判断方法：逻辑读远高于表行数，说明聚合并没走索引；CPU 时间占比高，说明聚合本身计算量大；扫描次数过多，说明源数据子查询里有关联或函数包裹。改完索引后把两组数字记下来对比，比口头讨论高效得多。另一个容易忽略的是隐式转换：如果年份字段定义成 VARCHAR，查询条件写 2023，SQL Server 会对列做 CONVERT，索引直接失效，这是 Excel 导入数据表场景最常见的隐患。
5. UNPIVOT 与 PIVOT 的组合运用
5.1 UNPIVOT 的标准语法与 NULL 陷阱
UNPIVOT 在官方文档里的定位是“透视的逆操作”，它把一个横表按两个新列展开回明细。标准写法：
SELECT 产品分类, 年份, 销量 FROM ( SELECT 产品分类, [2023] AS 销量2023, [2024] AS 销量2024 FROM #pivot_result ) AS 宽表 UNPIVOT ( 销量 FOR 年份 IN ([2023], [2024]) ) AS 反透视表;这里的 UNPIVOT 括号里只有一个聚合值列名和一个转换列定义。理解时把 IN 后面的老列名想成“要拆进年份列的值”，[2023] 这一列的值会进入销量，同时生成一行年份='2023' 的记录。列名因此必须和源 SELECT 的别名一致。
最大的坑是 UNPIVOT 默认忽略 NULL。如果 #pivot_result 里销量2024 是 NULL，UNPIVOT 结果里根本不会出现 2024 年的行，这个行为会直接导致“行数变少”的诡异现象。解决方法是在反透视前先处理 NULL，下面这张表总结了三种常见处理：
| 源值 | UNPIVOT 默认行为 | ISNULL(值,0) 后行为 | NULLIF(值,0) 后行为 |
|---|---|---|---|
| NULL | 不产生行 | 产生行且值为 0 | 不产生行 |
| 0 | 产生行且值为 0 | 产生行且值为 0 | 不产生行 |
| 正数 | 产生行 | 产生行 | 产生行 |
用 ISNULL 把 NULL 改成 0 后再 UNPIVOT，转换后保留零值行，报表口径“无记录=0”才能保持一致。如果业务认为无记录就应当被过滤，保持默认 NULL 行为即可。NULLIF 则用来把 0 也当成缺失过滤，适合那些“0 没有业务意义”的口径。这三种行为的取舍，要在写报表逻辑前先和业务对清楚。
5.2 先 PIVOT 再 UNPIVOT：宽表与纵向明细的转换
实际工作中经常遇到“外部报表要宽表，内部数据处理要窄表”的矛盾。业务方希望 Excel 里一个产品占一行，产品A、产品B、产品C 各占一列；数据仓库下游做聚合时又需要“产品名称+销量”纵向格式。解法就是先 PIVOT 转宽，再 UNPIVOT 转窄，两段组合成完整脚本：
SELECT 产品分类, 产品名称, 销量 FROM ( SELECT 产品分类, 产品A, 产品B, 产品C FROM ( SELECT 产品分类, 产品名称, 销量 FROM dbo.销售明细 WHERE 产品名称 IN ('产品A', '产品B', '产品C') ) AS 源数据 PIVOT ( SUM(销量) FOR 产品名称 IN ([产品A], [产品B], [产品C]) ) AS 透视表 ) AS 宽表 UNPIVOT ( 销量 FOR 产品名称 IN ([产品A], [产品B], [产品C]) ) AS 反透视表 WHERE 销量 > 0;这里的 UNPIVOT 把透视表再还原成明细，WHERE 销量 > 0 过滤掉零值，构成一套标准的清洗管道。Hive 里对应的做法是 collect_list 配合 explode，MySQL 8.0 里是 GROUP_CONCAT 加 JSON 拆分，SQL Server 的 UNPIVOT 好处是原生 SQL、没有额外函数依赖。要注意这个脚本里 PIVOT 和 UNPIVOT 的列名列表为了可读性用了静态值，如果产品会动态变化，把第 3 章的 FOR XML PATH 拼接逻辑套用过来，把 [产品A]...[产品C] 替换成动态列名即可。
6. 进阶技巧：PIVOT 配合窗口函数处理“最新值”场景
6.1 ROW_NUMBER 取每个分类的最新记录再做 PIVOT
先解决“每个产品分类最近一年的销量”，再行转列：
WITH 最新年度 AS ( SELECT 产品分类, 年份, 销量, ROW_NUMBER() OVER ( PARTITION BY 产品分类 ORDER BY 年份 DESC ) AS 序号 FROM dbo.销售明细 ) SELECT 产品分类, [2025] AS 最近年销量, [2024] AS 次新年销量 FROM ( SELECT 产品分类, 年份, 销量 FROM 最新年度 WHERE 序号 <= 2 ) AS 源数据 PIVOT ( SUM(销量) FOR 年份 IN ([2025], [2024]) ) AS 透视表;思路是先用 ROW_NUMBER 在“产品分类”分区里按年份倒序编号，取前两名，然后再 PIVOT；这样 PIVOT 处理的是每个分类最多两行的数据，SUM 不会误算重复值。窗口函数一定要放在 PIVOT 之前，如果先转置再排名，NULL 会让排名结果失效。
6.2 倒序动态列名与渲染顺序的一个小技巧
动态 PIVOT 的列顺序由 FOR XML PATH 里的 ORDER BY 决定，但有些报表系统按列名字母序渲染，导致 2025、2024、2023 的顺序被打乱。技巧：把 ORDER BY 方向设成业务需要的方向，同时在生成列表时用 AS 规范化列名：
SELECT @columns = STUFF( ( SELECT ',' + QUOTENAME(年份) + ' AS [' + CAST(年份 AS VARCHAR(4)) + '年销量]' FROM ( SELECT DISTINCT 年份 FROM dbo.销售明细 ) AS 年份表 ORDER BY 年份 DESC FOR XML PATH(''), TYPE ).value('.', 'NVARCHAR(MAX)'), 1, 1, '' );这样生成的列名是 [2025年销量]、[2024年销量]，报表系统按中文名排序时也能保持预期顺序。动态语句里多重拼接时，用 QUOTENAME 包原始列名、用 AS 定义展示名，两层关系不容易乱。如果某个报表系统对列顺序特别敏感，直接调整 ORDER BY 方向比在视图层重新排序更省事。
本文还有配套的精品资源，点击获取
