在SQL Server里做报表、做数据透视绕不开的一个操作就是行转列。比如你有一张销售流水表每个季度一行数据老板非要把四个季度变成四列一行一个年份这种需求每周都能遇到几次。网上搜“SQL Server 行转列”能搜出一堆代码但很多是片段照着抄完还不一定对尤其是动态列那块报错报得莫名其妙。这篇文章就把行转列这事讲透。你会看到最常见的三种做法PIVOT、CASE WHEN聚合、动态SQL拼接以及它们的适用场景和坑。我还会把生产环境里踩过的雷、排查思路一起写出来附上可以直接跑的SQL脚本。无论你用的是SQL Server 2008 R2还是2022只要数据库是Microsoft SQL Server系列这套逻辑基本通用适合刚接触透视表查询的开发者也适合想从“会抄”提升到“会写”的运维和数据分析同学。1. 先搞明白什么样子的数据需要行转列1.1 一个最常见的业务场景先看数据。假设我们有一张年度销售表结构非常简单年份、季度、销售额。YearNoQuarterNoAmount202111200.00202121500.00202131350.00202142100.00202211800.00202221650.00202232200.00202242400.00这是典型的“长表”结构一行表示一个季度。但报表需求通常长这样横轴是季度纵轴是年份每个季度一列一眼能看到2022年四个季度的对比。YearNoQ1Q2Q3Q420211200.001500.001350.002100.0020221800.001650.002200.002400.00把数据行变成结果集的列这就是“行转列”。这个操作在关系数据库里没有一个叫ROWTOCLOUMN的现成函数而是要靠聚合函数加条件逻辑或者用专门的PIVOT操作符来实现。1.2 行转列的本质是什么行转列的本质不是“把表横过来”而是“把某一列的唯一值作为新列对另一列的值做聚合”。随便截取一行来说2021年有4条记录季度分别是1、2、3、4销售额分别是1200、1500、1350、2100。转列之后4个季度的值被拆成了4个字段而每个字段的值就是“对应季度下的聚合结果”。这里其实隐藏着一个分组逻辑除了被转成列的字段季度和值字段销售额之外其他字段年份就是分组依据。理解这一层后面的所有写法都会变得清清楚楚。很多人在PIVOT上翻车就是因为没搞明白它内部到底怎么分组、怎么聚合。2. 静态PIVOT写法固定列行转列最省事的方案2.1 建表与样本数据先建一张测试表数据就用上面那份。我习惯在tempdb里做验证避免弄脏业务库。USE tempdb; GO IF OBJECT_ID(dbo.Sales, U) IS NOT NULL DROP TABLE dbo.Sales; GO CREATE TABLE dbo.Sales ( YearNo INT, QuarterNo TINYINT, Amount DECIMAL(10, 2) ); GO INSERT INTO dbo.Sales (YearNo, QuarterNo, Amount) VALUES (2021, 1, 1200.00), (2021, 2, 1500.00), (2021, 3, 1350.00), (2021, 4, 2100.00), (2022, 1, 1800.00), (2022, 2, 1650.00), (2022, 3, 2200.00), (2022, 4, 2400.00); GO2.2 PIVOT核心语法与执行逻辑固定四个季度最直接的写法就是PIVOTSELECT YearNo, [1] AS Q1, [2] AS Q2, [3] AS Q3, [4] AS Q4 FROM ( SELECT YearNo, QuarterNo, Amount FROM dbo.Sales ) AS Src PIVOT ( SUM(Amount) FOR QuarterNo IN ([1], [2], [3], [4]) ) AS Pvt;这段SQL的执行逻辑我拆开讲。先看FROM里的子查询它只挑了三个列出来YearNo、QuarterNo、Amount。为什么不能直接SELECT *因为PIVOT内部的逻辑是除了“FOR后面的透视列”和“聚合的值列”剩下所有列都自动作为分组列。假如你在子查询里多加一列主键ID那结果就会变成“按年份ID分组再透视”本来预期的2行结果会变成8行每一行都只有一个季度的值看起来就是错乱的。再看PIVOT括号内部的三段SUM(Amount)指定了值列和聚合方式FOR QuarterNo声明了用哪个列的唯一值来生成新列IN ([1],[2],[3],[4])则明确写出“我要把季度的哪些值变成列”。注意IN列表里必须用方括号把值包起来因为这里面的写法是“列名”语义而不是查询条件。如果你写成IN (1,2,3,4)SQL Server不会直接报错但结果往往不是你要的等于在找一列名字叫“1”的列。2.3 静态PIVOT的适用边界静态PIVOT适合列固定不变、数量不多、且你能手写出来的场景。季度永远只可能有1到4用PIVOT很合适月份也固定12个手写也还好。但如果你要透视的列来自业务数据比如“把每个产品的销量变成一列”产品种类有几十个今天可能有苹果明天可能多了香蕉那静态SQL根本维护不了必须走动态拼接。还有一个场景是多指标透视比如同时要销售额和销售量两列PIVOT不能直接在一个透视块里转两个值列你可能会被迫写两个PIVOT再做JOIN或者干脆换case方案。这些我在后面会展开。注意PIVOT 语法从 SQL Server 2005 开始就有2008 R2 到 2022 全系列通用和当前主流版本没兼容问题。3. 替代方案CASE WHEN聚合比PIVOT更好调试3.1 等价的CASE写法在PIVOT之外还有一种非常通用的写法就是在聚合函数里写CASE WHEN有些人叫“条件聚合”。同样查销售数据SQL长这样SELECT YearNo, SUM(CASE WHEN QuarterNo 1 THEN Amount END) AS Q1, SUM(CASE WHEN QuarterNo 2 THEN Amount END) AS Q2, SUM(CASE WHEN QuarterNo 3 THEN Amount END) AS Q3, SUM(CASE WHEN QuarterNo 4 THEN Amount END) AS Q4 FROM dbo.Sales GROUP BY YearNo;结果和PIVOT一模一样。而且这里的THEN Amount后面没有ELSE 0是故意不写的因为CASE WHEN不匹配时返回NULLSUM会忽略NULL值效果等同于“当前季度有数据就加没数据就不加”。3.2 为什么我很多时候推荐用CASE WHEN静态列转行时我的默认选择往往是CASE WHEN而不是PIVOT原因有三点。第一它不要求框定“子查询的列范围”。PIVOT你多选一列就出问题而CASE WHEN直接对原表做GROUP BY分组列写清楚就行不会因为表里多了字段就翻车调试成本低。第二它天然支持多个值列。比如要同时出销售额和订单数直接多写两个字段就行SELECT YearNo, SUM(CASE WHEN QuarterNo 1 THEN Amount END) AS Q1_Amount, COUNT(CASE WHEN QuarterNo 1 THEN OrderId END) AS Q1_Cnt FROM dbo.Sales GROUP BY YearNo;PIVOT写多指标要先转一次再JOIN一次CASE WHEN一条SQL搞定。第三执行计划更容易看懂。PIVOT的聚合器在某些版本里会生成额外的“透视运算”操作符而CASE WHEN最终就是常规的Stream Aggregate或Hash Aggregate。真碰上性能问题CASE WHEN更直观调优时能一眼看穿瓶颈。当然CASE WHEN也不是没有缺点。如果列非常多SQL文本会很长很啰嗦而且当你不知道具体有哪些列时它一样需要动态拼接。也就是说当列数量少且固定时我首选CASE WHEN当列多但固定时PIVOT可读性反而更好当列不固定时两者都需要动态SQL。4. 动态列的行转列STUFF FOR XML PATH 拼接列清单4.1 一个能跑通的动态PIVOT模板前面两种写法都假设季度一共4个但实际业务里经常出现“这个月突然多了个新产品变成5列了”的情况。这时候就要在运行期查出去重后的列值再拼SQL。下面这段模板可以直接复用它做的事情分四步先去重查所有季度用FOR XML PATH把值拼成带方括号的列列表把它嵌进PIVOT语句最后用sp_executesql执行。DECLARE cols NVARCHAR(MAX), sql NVARCHAR(MAX); SELECT cols STUFF(( SELECT DISTINCT , QUOTENAME(QuarterNo) FROM dbo.Sales FOR XML PATH(), TYPE ).value(., NVARCHAR(MAX)), 1, 1, ); SET sql N SELECT YearNo, cols N FROM ( SELECT YearNo, QuarterNo, Amount FROM dbo.Sales ) AS Src PIVOT ( SUM(Amount) FOR QuarterNo IN ( cols N) ) AS Pvt; ; PRINT sql; EXEC sp_executesql sql;跑完之后生成的列清单大概是[1],[2],[3],[4]动态SQL拼出来等价于第二节的静态版本。如果8月份出现了第5季度当然业务上不会但假设会列清单就会自动变成[1],[2],[3],[4],[5]查询结果也会跟着多出一列。4.2 动态拼接的3个关键细节先说STUFF和FOR XML PATH这段。FOR XML PATH()的作用是让多行查询结果拼接成一行字符串每行前面加一个逗号最终得到的是, [1],[2],[3],[4]这样的文本然后STUFF从第1个字符开始替换掉1个字符把最前面那个逗号吃掉。写这段的时候有经验的DBA会特别小心几个点。第一FOR XML PATH后面最好加TYPE然后用.value(., NVARCHAR(MAX))把XML转成字符串。如果不加TYPE拼接结果会被隐式转换成NVARCHAR(4000)数据一多就截断动态SQL少一截执行直接报语法错误而且这种错误很难肉眼发现。第二列值一定要用QUOTENAME包起来。季度值虽然是数字包不包都能跑但如果列值是中文、带空格的字符串或者包含保留字的字段不包就会炸。养成习惯任何列值都过一遍QUOTENAME还能防一手SQL注入。第三执行动态SQL优先用sp_executesql而不是EXEC因为sp_executesql可以参数化对执行计划复用好一些。更重要的是调试动态SQL时先PRINT再EXEC把生成的SQL打出来人工检查一遍再执行。我以前犯过“直接在EXEC里跑报错之后靠猜来猜去”的毛病浪费时间。4.3 当列值本身是字符串时怎么办业务里透视的字段不一定是数字季度也可能是城市名、产品名这类字符串。动态 SQL 的拼接逻辑其实一样但有几个细节要格外注意。比如现在要把“城市”转成列去重拼列的语句不变用QUOTENAME(城市名) 就会得到[北京],[上海],[广州]PIVOT 里的写法也不变。但如果你在CASE WHEN动态方案里拼接就要注意给每个枚举值加单引号否则SQL会把“北京”当成列名去解析。我的习惯是字符串列优先用PIVOT动态方案因为QUOTENAME同时解决了“列名合法”和“字符串转义”两个问题如果必须用CASE WHEN方案拼接要写成CASE WHEN City 北京 THEN Amount END这里两个单引号在动态SQL字符串里表示一个单引号非常容易写错除非有现成框架否则我宁可绕开。5. UNPIVOT把转置后的表再转回去5.1 UNPIVOT基础写法行转列解决了列转行也常常要用到。比如第三方系统给你一张已经透视好的表或者你从Excel导进来一个“年份四个季度”的表但你的报表组件只接受长表这个时候就要做UNPIVOT。先造一个已经转置的表IF OBJECT_ID(dbo.SalesPivot, U) IS NOT NULL DROP TABLE dbo.SalesPivot; GO CREATE TABLE dbo.SalesPivot ( YearNo INT, Q1 DECIMAL(10, 2), Q2 DECIMAL(10, 2), Q3 DECIMAL(10, 2), Q4 DECIMAL(10, 2) ); INSERT INTO dbo.SalesPivot (YearNo, Q1, Q2, Q3, Q4) VALUES (2021, 1200.00, 1500.00, 1350.00, 2100.00), (2022, 1800.00, 1650.00, 2200.00, 2400.00);然后做UNPIVOTSELECT YearNo, QuarterKey, Amount FROM ( SELECT YearNo, Q1, Q2, Q3, Q4 FROM dbo.SalesPivot ) AS Src UNPIVOT ( Amount FOR QuarterKey IN (Q1, Q2, Q3, Q4) ) AS Unp;可以看到UNPIVOT 的语法结构和 PIVOT 非常像区别在于Amount FOR QuarterKey IN (Q1,Q2,Q3,Q4)表示“把Q1到Q4这几列的列名变成QuarterKey字段的值把这几列的值统一放到Amount字段里”。5.2 UNPIVOT的两个坑类型不一致和NULL丢失坑一所有要转的列数据类型必须一致。如果Q1是DECIMALQ4是INTSQL Server不会自动帮你转直接报错。实际从Excel导入的数据特别容易碰上这类“看起来是数字其实有的列是字符串”的情况所以UNPIVOT前我一般先写CAST统一类型。坑二UNPIVOT会丢NULL。某年的Q3字段如果是NULL转出来之后这行直接就消失了相当于你少了一条记录。如果你需要保留NULL行手工拼UNION ALL可能是更可控的选择SELECT YearNo, Q1 AS QuarterKey, Q1 AS Amount FROM dbo.SalesPivot UNION ALL SELECT YearNo, Q2, Q2 FROM dbo.SalesPivot UNION ALL SELECT YearNo, Q3, Q3 FROM dbo.SalesPivot UNION ALL SELECT YearNo, Q4, Q4 FROM dbo.SalesPivot;UNPIVOT背后的逻辑其实就是把多个列变成多个行它适合列固定、类型一致的常规场景。反过来如果你的数据本身就是一堆单独字段想转成表结构UNION ALL是更朴素的方案。6. 生产环境踩坑实录PIVOT的7个常见问题6.1 子查询里多了列导致结果错乱这是我见过最多次的错误。有人在子查询里直接SELECT *而原表有主键ID、备注、创建时间结果PIVOT把所有没在FOR和聚合里出现的列全当成了分组列。表面上看SQL没报错但查出来的行数翻了好几倍数值也不是预想的那样。排查方法很简单把PIVOT的子查询先单独跑一遍确认里面的列只有三类——分组列、透视列、值列。多一个字段结果就变一次这个“多列隐式分组”的机制必须刻在脑子里。6.2 动态列拼接结果被截断动态SQL列很多的时候如果FOR XML PATH没加TYPE字符串会被隐式截断到NVARCHAR(4000)。症状非常诡异PRINT出来的SQL看起来是完整的EXEC却报“字符串或二进制数据将被截断”或者干脆提示sql附近有语法错误。我现在的习惯是统一写成FOR XML PATH(), TYPE ).value(., NVARCHAR(MAX))不要省TYPE不要图省事直接转字符串。这是踩过几次坑之后养成的肌肉记忆。6.3 WHERE条件放错位置导致结果多出NULL列动态PIVOT里的IN列表来自去重后的列值如果源表有针对日期的WHERE条件你可能只在最内层子查询里过滤了那去重拼列的时候还是全量数据拼出来的列会比实际需要得多。那些“有史以来出现过但现在没有数据”的季度就会生成全是NULL的空列。遇到这种情况确认下业务是不是真的需要空列。如果不需要把去重查询和PIVOT子查询的过滤条件统一如果担心动态拼接时遗漏列反而应该保留空列。各有利弊但至少你得知道为什么会出现多余列。6.4 直接PIVOT前过滤掉NULL导致聚合错误行转列时聚合函数是SUM如果某些季度没有数据一般你在PIVOT之后看到NULL这是对的。但如果你在子查询里提前用了WHERE过滤掉NULL行可能会导致某一季度的行彻底消失结果列里出现NULL而不是0前端报表再对这个值做计算就容易出问题。所以一个原则是行转列尽量保留原始明细不要为了“看起来干净”提前过滤空值。至于结果里NULL和0用哪个统一在透视之后做ISNULL处理。6.5 字符串列值含特殊符号当透视列的值是字符串而且里面带了逗号、单引号、中文括号这些内容时手工拼IN列表特别容易出错。动态方案用QUOTENAME能挡掉一大半但如果列值是外部传入的比如从接口读取的城市名拼接前还需要做合法性校验至少不能用没过滤的字符串直接拼进SQL。我的建议是凡是不在自己控制之下的字符串列值统统一律走QUOTENAME必要时再加白名单校验。这不是危言耸听动态SQL一旦把非法文本拼进去轻则报错重则就是注入风险。6.6 多值列PIVOT的别扭写法需求经常是两个指标一起透视比如销售额和订单量。上面说过PIVOT不能在一个透视块里转两个值列有些人的做法是写两个PIVOT再JOINSELECT p1.YearNo, p1.[1] AS Q1_Amt, p2.[1] AS Q1_Cnt FROM (...第一指标PIVOT...) p1 JOIN (...第二指标PIVOT...) p2 ON p1.YearNo p2.YearNo;这样能跑通但SQL很长性能也不一定好。遇到这种情况我基本直接用多组CASE WHEN不仅短还能保持单次扫描。6.7 大数据量场景不要迷信PIVOT测试表数据量小PIVOT和CASE WHEN看不出差别。但真实生产里如果有几百万行PIVOT的执行计划有时会出现一个叫“透视运算”的运算符它本质上是分组聚合但内部实现不一定比你手写分组高效。举一个实际优化过的场景一张订单表2000万行按月份行转列原来用PIVOT跑8秒后来改成CASE WHEN聚合执行计划少了透视运算符直接扫描复用索引跑3秒出头。当然这不是说PIVOT永远慢而是提醒大家别把一个写法教条化。优化时两个方案都跑一遍执行计划用实际耗时说话。这里再给一个索引建议如果行转列经常按年份和季度过滤可以建一个覆盖索引CREATE NONCLUSTERED INDEX IX_Sales_Year_Qtr ON dbo.Sales (YearNo, QuarterNo) INCLUDE (Amount);这样无论PIVOT还是CASE WHENGathering数据时都能走索引覆盖扫描成本低不少。7. 一点个人操作习惯做行转列这几年我慢慢养成了几个固定习惯固定且列少的场景直接CASE WHEN固定但列很多、需要清晰列清单时用静态PIVOT列不固定时用STUFF加FOR XML PATH的动态PIVOT但所有动态SQL必须先PRINT再执行涉及多个指标时永远优先考虑CASE WHEN。另外拿到“行转列”需求不要急着写SQL先问清楚两个问题列到底是什么答案很可能随时间变前端报表能不能接受NULL还是必须补0。这两个问题决定了你选静态还是动态方案、要不要在透视后做ISNULL非常关键。上面所有脚本我都整理成了可直接运行的版本SQL Server 2008 R2到2022都能跑。你在自己环境里验证的时候可以故意在Sales表里多插几行数据比如把某个季度的值改成NULL再把子查询里多加一列亲眼看看结果怎么变很快就能把PIVOT的脾气摸透。