做报表的人十有八九会被“SQL Server 行转列”这件事绊一下。简单说行转列就是把一张“纵表”——每一行是一个维度值、一个指标值——重新整理成“横表”让某些行的取值变成列名比如把“季度”字段里的 Q1、Q2、Q3、Q4 分别变成四列每一行是一个销售员的全年汇总。这种需求在销售报表、绩效透视、权限矩阵、财务对账里太常见了面试也爱考。这篇文章我就从原理、三种主流写法、完整实操到踩坑排查把 SQL Server 里行转列这件事彻底讲透适合刚开始写 T-SQL 的开发者也适合那些已经用 CASE WHEN 写过、但想搞懂 PIVOT 和动态 SQL 的人参考。1. 行转列到底在解决什么问题——先搞懂需求和场景1.1 纵表和横表其实是两种完全不同的存储逻辑关系型数据库设计第一原则就是“一行只表达一个事实”所以业务表天然是纵表拿销售表举例一行就是一个销售员的某个季度销售额再带一个季度字段。纵表的优点特别明显——要加一个季度直接插入一行数据就行完全不需要动表结构。但缺点也很直白人眼看不惯。人眼习惯的横表是一行一个销售员左边是销售员名字右边依次排开 Q1、Q2、Q3、Q4 四列想看谁全年怎么样一目了然。所以行转列这件事本质是“存储结构”和“展示结构”之间的一次转换。数据库存纵表报表要横表行转列就是这个中间翻译官。1.2 真实业务场景报表统计、对账核对、权限矩阵我把这些年见过的实际需求列一下你们对号入座销售月度汇总把每个人 1 到 12 月的销售额转成 12 列最后一行还能放合计。科目余额表财务上经常要把“借方、贷方、余额”这种纵向科目转成横向字段。权限配置矩阵用户一行功能模块一列单元格里存有没有权限转出来就是一张权限清单。两列对账比如一张表只有“项目名、金额”两个字段但项目名里有“工资、社保、公积金、房租”要对账时就需要把它们各占一列。这类需求有一个共同点目标列的数量是业务上可枚举的或者至少是运行前能查到的。这个判断决定你后面选静态写法还是动态写法。1.3 这篇文章你能拿到什么我会把 SQL Server 里三种主流行转列方案都讲一遍CASE WHEN 条件聚合、PIVOT 运算符、动态 SQL 拼接。每种方案我都给完整可跑的示例用同一份数据方便你对比。文章最后还整理了一份踩坑速查表你以后遇到行转列问题可以直接翻。2. 三种主流实现方案我为什么这么选2.1 方案ACASE WHEN 条件聚合最老派也最通用CASE WHEN 方案的核心思路是“按目标列逐一判断再用聚合函数收口”。先看代码SELECT sales_person, SUM(CASE WHEN quarter Q1 THEN amount ELSE 0 END) AS Q1销售额, SUM(CASE WHEN quarter Q2 THEN amount ELSE 0 END) AS Q2销售额, SUM(CASE WHEN quarter Q3 THEN amount ELSE 0 END) AS Q3销售额, SUM(CASE WHEN quarter Q4 THEN amount ELSE 0 END) AS Q4销售额 FROM dbo.sales_detail GROUP BY sales_person ORDER BY sales_person;逻辑非常直白按销售员分组组内遇到 Q1 就把金额累加不是 Q1 就记 0。你可以把它理解成一个“手工透视表”每一列都是你自己写出来的。这个方案的最大优点是没有版本限制SQL Server 2000 时代就这么写2019、2022 依然能用而且每一列的逻辑完全可控你甚至可以针对不同的列写不同的判断条件。缺点也很明显目标列必须提前写死如果季度是动态的这方案就僵住了。另外如果转置的是字符串而不是数值SUM 就不能用了得看下面第 3 节里讲的 MAX 技巧。2.2 方案BPIVOT 运算符最直观但要守规矩PIVOT 是 SQL Server 2005 引入的专门语法就是为了解决行转列场景。它的基本格式是三段式先选数据源、再指定聚合函数、最后列出转为列名的取值列表。SELECT sales_person, [Q1] AS Q1销售额, [Q2] AS Q2销售额, [Q3] AS Q3销售额, [Q4] AS Q4销售额 FROM ( SELECT sales_person, quarter, amount FROM dbo.sales_detail ) AS SourceTable PIVOT ( SUM(amount) FOR quarter IN ([Q1], [Q2], [Q3], [Q4]) ) AS PivotTable;很多人第一次写 PIVOT 会报错主要就是没理解它的执行顺序。PIVOT 的工作方式是这样的先对源数据做分组分组依据是“除了 FOR 列和聚合列之外的所有列”然后对每组里 FOR 列的每个取值用聚合函数算出一个值。换句话说PIVOT 里藏着一个隐式的 GROUP BY它不会写在 SQL 里而是靠“剩下哪些列”推断出来的。这个隐藏的分组逻辑既是 PIVOT 简洁的原因也是它容易出坑的地方。比如你从源表直接选了三列以上PIVOT 就会把多余列当成分组列结果十有八九不是你要的。所以我的习惯是PIVOT 的源数据先只选出三列不多不少一行标识列、一个转置列、一个聚合列。宁可外面再多关联补列也别把额外列塞进源数据里。2.3 方案C动态SQL 拼接列不固定时的终极手段前面两种方案目标列都是手写的。可如果转置列的值是运行期才能确定的呢比如今天数据库里只有 Q1 到 Q4下个月突然多了一个“Q5调整季度”那静态 SQL 就废了。这时候只能动态拼 SQL。DECLARE columns NVARCHAR(MAX), sql NVARCHAR(MAX); SELECT columns STRING_AGG(QUOTENAME(quarter), ,) FROM (SELECT DISTINCT quarter FROM dbo.sales_detail) AS q; SET sql N SELECT sales_person, columns N FROM ( SELECT sales_person, quarter, amount FROM dbo.sales_detail ) AS src PIVOT ( SUM(amount) FOR quarter IN ( columns N) ) AS pvt;; EXEC sp_executesql sql;动态 SQL 不太好理解我拆开讲。第一步先从业务表里把所有不重复的 quarter 查出来用 STRING_AGG 拼接成逗号分隔的列清单并把每个值用 QUOTENAME 包上中括号得到类似[Q1],[Q2],[Q3],[Q4]的字符串。第二步把这个字符串嵌进一条 PIVOT 语句的文本里然后交给 sp_executesql 去执行。如果你用的还是 SQL Server 2016 或更早版本没有 STRING_AGG可以用 FOR XML PATH 的经典写法SELECT columns STUFF( ( SELECT , QUOTENAME(quarter) FROM (SELECT DISTINCT quarter FROM dbo.sales_detail) AS q ORDER BY quarter FOR XML PATH() ), 1, 1, );STUFF 函数的作用是把拼出来的字符串第一个逗号替换成空字符这也是老版本拼接字符串最主流的做法。动态 SQL 的优点是灵活缺点是调试难、可读性差而且有 SQL 注入风险。所以使用动态 SQL 时我给自己立了几条规矩第一列清单必须是数据库里查出来的真实取值不能直接拼接用户输入第二每个列名必须用 QUOTENAME 包一层第三能用参数传值的地方比如 WHERE 条件一律用 sp_executesql 的参数列表不要直接写进字符串。2.4 三种方案横向对比对比项CASE WHEN 条件聚合PIVOT 运算符动态 SQL 拼接上手难度最低会 SUM 就会用中等偏语法化较高要理解字符串拼接执行可读性列越多越长但每列直白结构紧凑逻辑清晰拼接后不易阅读目标列灵活性列写死列写死运行时自动取列版本兼容性全版本通用2005 及以上取决于拼接函数版本调试友好度高可逐步排查中报错较抽象低需打印 SQL 分析典型适用场景列固定、逻辑复杂列固定、不想写一堆 CASE列动态、自动适配增减我平时选型的规则很简单列固定、逻辑复杂用 CASE WHEN列固定、追求简洁用 PIVOT列不固定一律动态 SQL。实际生产环境里CASE WHEN 的使用率其实比 PIVOT 高不少因为它调试方便出问题一眼就能看出是哪一列算错了。3. 一个真实场景从零到一的完整实操3.1 先造一份数据季度销售明细为了演示三种方案我先建一张销售明细表包含销售员、季度、大区和销售额四个字段。特意留几个空档比如李四没有 Q3、王五没有 Q1这样后面 NULL 处理才有得讲。CREATE TABLE dbo.sales_detail ( id INT IDENTITY(1,1) PRIMARY KEY, sales_person NVARCHAR(50) NOT NULL, quarter CHAR(2) NOT NULL, region NVARCHAR(50) NULL, amount DECIMAL(12,2) NOT NULL ); INSERT INTO dbo.sales_detail (sales_person, quarter, region, amount) VALUES (N张三, Q1, N华东, 12000.00), (N张三, Q2, N华东, 14500.00), (N张三, Q3, N华东, 13200.00), (N张三, Q4, N华东, 16800.00), (N李四, Q1, N华北, 9800.00), (N李四, Q2, N华北, 13000.00), (N李四, Q4, N华北, 15100.00), (N王五, Q2, N华南, 14200.00), (N王五, Q3, N华南, 9800.00), (N王五, Q4, N华南, 17600.00);这里季度字段我用 CHAR(2)存 Q1 这种固定两位的值存储开销比 NVARCHAR 小排序也符合直觉。如果业务里可能出现“Q10”那就必须改成 NVARCHAR 或补齐三位否则排序会出问题——这个问题后面还会再提。3.2 用CASE WHEN 实现先跑通再优化SELECT sales_person, SUM(CASE WHEN quarter Q1 THEN amount ELSE 0 END) AS Q1销售额, SUM(CASE WHEN quarter Q2 THEN amount ELSE 0 END) AS Q2销售额, SUM(CASE WHEN quarter Q3 THEN amount ELSE 0 END) AS Q3销售额, SUM(CASE WHEN quarter Q4 THEN amount ELSE 0 END) AS Q4销售额 FROM dbo.sales_detail GROUP BY sales_person ORDER BY sales_person;结果sales_personQ1销售额Q2销售额Q3销售额Q4销售额张三12000.0014500.0013200.0016800.00李四9800.0013000.000.0015100.00王五0.0014200.009800.0017600.00注意这里 ELSE 0 的效果没有 Q1 销售记录的王五Q1 列显示 0.00而不是 NULL。这个细节很重要报表上“没有业绩”和“数据缺失”可能是两个完全不同的业务含义。如果你希望缺失季度的格子保持空白把 ELSE 0 删掉CASE 语句在条件不满足时就会返回 NULLSUM 会自动忽略 NULL结果就是 NULL可以在 SELECT 外层再套 ISNULL。如果要把字符串类型的字段转列SUM 就行不通了因为字符串不能求和。比如有一张表记录每个员工参加的培训项目要把“内训、外训、线上课”转成三列存培训名称就得换成 MAXSELECT employee_id, MAX(CASE WHEN course_type 内训 THEN course_name END) AS 内训课程, MAX(CASE WHEN course_type 外训 THEN course_name END) AS 外训课程, MAX(CASE WHEN course_type 线上课 THEN course_name END) AS 线上课程 FROM dbo.train_record GROUP BY employee_id;这里的原理是分组后同组内每个 CASE 表达式最多只有一个非 NULL 值MAX 就是把这个非 NULL 值捞出来。3.3 用PIVOT 实现注意派生表的三列结构SELECT sales_person, ISNULL([Q1], 0) AS Q1销售额, ISNULL([Q2], 0) AS Q2销售额, ISNULL([Q3], 0) AS Q3销售额, ISNULL([Q4], 0) AS Q4销售额 FROM ( SELECT sales_person, quarter, amount FROM dbo.sales_detail ) AS SourceTable PIVOT ( SUM(amount) FOR quarter IN ([Q1], [Q2], [Q3], [Q4]) ) AS PivotTable ORDER BY sales_person;如果你把这段 SQL 和 3.2 对比会发现结果完全一致。PIVOT 唯一的坑就是源数据列数一定不能多。我这里刻意没有把 region 字段选进派生表因为一旦选进去PIVOT 会认为你要按“销售员大区”两个维度分组同一个销售员就可能出现多行。我见过最典型的生产案例就是有人为了省事直接SELECT * FROM 业务表作为 PIVOT 源数据结果查询跑出来十几行重复数据还找不到原因。真凶就是表里那些看起来“无关紧要”的备注字段、创建时间字段全被 PIVOT 当成了分组列。3.4 用动态SQL 实现列清单自动生成接下来动真格的了。假设季度值不是固定的 Q1-Q4而是以后还会出现 Q5、Q6静态 SQL 每次都要改。动态 SQL 的做法是让列清单自己从数据里长出来DECLARE columns NVARCHAR(MAX), sql NVARCHAR(MAX); SELECT columns STRING_AGG(QUOTENAME(quarter), ,) FROM (SELECT DISTINCT quarter FROM dbo.sales_detail) AS q; SET sql N SELECT sales_person, columns N FROM ( SELECT sales_person, quarter, amount FROM dbo.sales_detail ) AS src PIVOT ( SUM(amount) FOR quarter IN ( columns N) ) AS pvt;; EXEC sp_executesql sql;这段 SQL 的关键点在于 QUOTENAME(quarter)。QUOTENAME 会把值包成[Q1]这样的合法标识符如果列名撞上 SQL Server 关键字比如有个季度值叫Q1而某业务字段叫SELECT没有 QUOTENAME 就会直接报语法错误。同时动态 SQL 拼接的列顺序是随 DISTINCT 扫描出来的实际使用中如果要固定列顺序需要在子查询里加 ORDER BYSELECT columns STRING_AGG(QUOTENAME(quarter), ,) FROM ( SELECT DISTINCT quarter FROM dbo.sales_detail ) AS q;如果你用 FOR XML PATH 的版本排序写在子查询里效果是一样的。反正原则是动态 SQL 里能显式控制的都不要靠运气。调试动态 SQL 也有笨办法就是把拼好的字符串先 SELECT 出来看PRINT sql;在 SSMS 的“消息”面板里能看到完整的 SQL 文本然后复制出来执行看是不是你要的效果。我每次写动态 SQL 都会先 PRINT 一遍确认列清单拼接正确后再交给 EXEC这一步能省掉大量摸黑调试的时间。3.5 实操中的性能细节与索引建议行转列本质是分组聚合所以性能优化思路和普通聚合查询是一样的。先看重灾区如果 sales_detail 表很大查询条件里又没有过滤三种方案都会扫全表。我的建议是在进入 PIVOT 或 CASE WHEN 之前先尽量缩小数据范围比如加一个日期范围的 WHERE 条件让源数据变少。索引方面最适合这种查询的索引是组合索引把分组列和 FOR 列放在前面把聚合列放到 INCLUDE 里CREATE NONCLUSTERED INDEX ix_sales_person_quarter ON dbo.sales_detail (sales_person, quarter) INCLUDE (amount);这样查询按销售员分组、再按季度取值时可以直接从索引里拿到 amount不需要回表。需要注意如果你在 WHERE 里过滤 region 字段那索引前导列就得重新考虑可以把 region 也加进索引具体看业务查询模式。另外我实测过CASE WHEN 和 PIVOT 在同样数据量下的执行计划基本一致它们底层都会走 Stream Aggregate 或 Hash Aggregate。动态 SQL 如果只是列清单不同执行计划也和静态 PIVOT 差不多。所以性能上不要纠结选哪个真正影响性能的是你有没有过滤条件、有没有合适的索引。4. 行转列最容易踩的坑与排查实录4.1 分组列选不对结果多出重复行这是 PIVOT 方案翻车率最高的一个问题。症状是明明转出来了但同一个销售员出现好几行金额还都对不上。原因就是 PIVOT 源数据里混入了多余的列比如我把 region 选了进去每个销售员在华东、华北、华南各占一行结果当然不对。排查方法很简单把 PIVOT 源数据单独执行一遍去掉聚合直接看原始行数。如果业务上确实需要保留多个维度那就明确把这些维度都放进外层 SELECT 的分组语义里而不是靠 PIVOT 的“剩余列”机制去隐式分组。CASE WHEN 方案遇到重复行往往是 GROUP BY 漏了列。比如同样按销售员统计但数据源里一个销售员有多条同季度记录你 GROUP BY sales_person 后每季度求和结果是对的可如果分组少了一个维度聚合粒度就变粗了也会出问题。写 GROUP BY 的时候多问自己一句这个粒度是不是我要的4.2 聚合函数用错SUM/MAX/COUNT各怀心思转数值列大家自然用 SUM但转字符串列很多人就卡住了。字符串不能 SUM只能用 MAX 或 MIN原理是每组只有一个非 NULL 值。这个技巧前面已经演示过。COUNT 则更特殊COUNT(CASE WHEN 条件 THEN 1 END) 统计的是满足条件的记录数不是值本身所以做“有几个季度有业绩”这类需求时很有用。PIVOT 语法里聚合函数只能写一个而且不能写 AVG 之外的复杂条件。如果业务上需要同时转两个指标比如既要销售额又要订单数PIVOT 就有点力不从心。我一般会转两次再用 JOIN 合并或者干脆退回 CASE WHEN写两组 SUM 列反而简单。4.3 列名撞上关键字PIVOT直接报语法错误如果 FOR 列里的值刚好是 SQL Server 的保留字比如某业务取值叫SELECT、TABLE、ORDER直接写在 IN 列表里就会报错。解决办法是用中括号把它们包起来[SELECT]。如果这些值是动态的QUOTENAME 会自动加中括号这就是我前面强调 QUOTENAME 不省略的原因。还有一种容易忽略的情况列名带空格或者中文。中文字段作为别名是合法的但如果你拼接动态 SQL 时忘记加中括号SQL 引擎可能把它拆成多个词立刻报错。总之凡是进入 IN 列表的值统一加中括号没有坏处。4.4 NULL 不是 0转列后一堆空白怎么补转列之后最常被问的问题就是为什么我的 PIVOT 结果是空白不是 0因为 PIVOT 对没有对应记录的单元格返回 NULLCASE WHEN 不加 ELSE 也是 NULL。报表里 NULL 显示为空老板看了以为数据丢了。处理方式看场景。业务上明确“没有业绩就是 0”的用 ISNULL 或 COALESCE 包一层如果 NULL 有特殊含义比如“该季度未纳入考核”就不要强转 0否则数据失真。我遇到过一个项目因为把 NULL 全转成了 0最后平均数被拉低一大截排查了半天才发现是转列环节埋的雷。4.5 动态SQL 的字符串拼接和类型问题动态 SQL 有三个常见坑。第一个是列清单重复如果源表里有重复的 quarter 值DISTINCT 没写就会拼出重复列SQL 直接报错。第二个是列清单为空如果表里没有任何数据columns 就是 NULL拼接出来一条残缺的 SQL。第三个是拼接字段长度NVARCHAR(MAX) 不会截断但如果你习惯用 NVARCHAR(100)列一多就被截断连报错都很诡异。给动态 SQL 加个保险拼接前判断一下IF NULLIF(columns, ) IS NULL BEGIN THROW 51000, 没有可转置的列请检查源数据, 1; END这段保护逻辑在动态列转置里非常值得养成习惯。毕竟生产环境的数据永远比你想象中更“脏”。4.6 常见问题速查表现象可能原因解决方向转置后出现重复行PIVOT 源数据混入多余列或 CASE WHEN 漏分组列源数据只保留三列GROUP BY 逐列核对粒度字符串列无法 SUM转置列的字段类型不是数值改用 MAX/MIN 捞非空值列名带关键字/空格报错未加中括号手写用[]动态用 QUOTENAME转置结果空白不是0NULL 未被处理按业务语义使用 ISNULL 或保持 NULL动态SQL报列重复DISTINCT 未写列清单子查询加 DISTINCT动态SQL执行结果为空列清单变量为 NULL加 NULLIF 判断并抛出异常PIVOT 列顺序乱IN 列表顺序就是列顺序手写排列顺序动态加 ORDER BY拼接字符串被截断变量用了短类型统一使用 NVARCHAR(MAX)5. 行转列之后的下一站从“转出来”到“用得爽”5.1 UNPIVOT把宽表再转回去行转列之后的表有时候下游系统不认又得转回纵表。SQL Server 提供了 UNPIVOT 运算符操作方向正好相反SELECT sales_person, quarter, amount FROM ( SELECT sales_person, [Q1], [Q2], [Q3], [Q4] FROM #pivot_result ) AS src UNPIVOT ( amount FOR quarter IN ([Q1], [Q2], [Q3], [Q4]) ) AS unpvt;UNPIVOT 不需要聚合函数它就是把宽表按 IN 列表里的列一行行拆开。需要注意UNPIVOT 会跳过 NULL 值所以如果你想把“无业绩”也保留下来得提前把 NULL 统一替换成 0 或者其他占位值。5.2 跨行字符串合并另一种“纵转横”严格说这不是行转列但很多人搜行转列时其实想要这个效果把每个人的所有季度记录拼成一行字符串。SQL Server 2017 以上可以用 STRING_AGGSELECT sales_person, STRING_AGG(quarter N: CONVERT(NVARCHAR(20), amount), N; ) WITHIN GROUP (ORDER BY quarter) AS 明细 FROM dbo.sales_detail GROUP BY sales_person;老版本继续用 FOR XML PATH原理是把子查询的结果拼成 XML 文本再去掉标签。这种写法在生成导出文件、拼接日志时非常实用建议收藏。5.3 与报表工具的矩阵透视互补行转列在数据层做完之后报表层其实还有更轻的办法。SSRS 的矩阵控件、Power BI 的矩阵视觉对象本身就有“透视”能力不需要你在 SQL 里转好列把纵表直接丢进去把季度拖到列字段区域报表工具会自动渲染成横表。我的经验是分场景取舍如果只是做展示优先让报表工具自己透视SQL 保持纵表这样维护最简单如果下游是 Excel 模板、固定格式导出、或者需要把转好的表再参与 JOIN那就在数据层用行转列转好。一句话能晚转就晚转展示层能解决的事不要提前在数据层复杂化。5.4 结合窗口函数处理复杂统计偶尔会遇到一种很刁钻的需求行转列后每一列还要显示每个季度相对上一个季度的增长率、或者显示季度排名。这种需求纯 PIVOT 写不出来因为 PIVOT 只能做一次聚合。我通常的做法是先在纵表阶段用窗口函数算出需要的值再转列。比如计算每个销售员每个季度销售额的排名SELECT sales_person, quarter, amount, RANK() OVER (PARTITION BY quarter ORDER BY amount DESC) AS 季度排名 FROM dbo.sales_detail;然后把结果拿来转列转出来的每一列就是“每个季度谁排第几”。这个思路的好处是窗口函数在纵表阶段算行转列只负责搬位置两个环节各干各的逻辑清晰得多。行转列不是银弹它只解决位置变换的问题真正的计算还是要在合适的数据形态下做这个顺序别搞反。最后分享一个我个人的实操体会遇到行转列需求第一件事不是打开 SSMS 写 PIVOT而是拿纸笔把目标表结构画出来确认清楚“哪一列是行标识、哪一列要变成列名、哪一列是聚合值”。这三个角色一定位方案自然就出来了。列固定就 CASE WHEN 或 PIVOT列不固定就动态 SQL。CASE WHEN 虽然看起来土但调试时你能一行行解释每个数字是怎么来的PIVOT 虽然简洁但对源数据列极其敏感稍不注意就是重复行。动态 SQL 永远放在最后考虑不是因为它难而是因为它把排错成本提高了不少。先跑通再优化最后才是想着“炫技”。