在SQL Server里行转列通常也叫PIVOT、透视是被问得最多的数据重排需求之一。业务侧看到的报表永远是宽表每个科目一列、每个月一列、每种属性一列可数据库里存的偏偏是长表一个学生一行一科一个订单一行一条明细。于是每次做报表开发本质上都在做行转列。这个问题的解法其实不多——条件聚合、PIVOT运算符、动态PIVOT、FOR XML PATH拼接满打满算四类但每一类里都有不少细节坑。这篇我围绕成绩表和联系方式表两个业务场景把这四类写法的适用条件、核心语法、参数含义、性能表现一次讲透并给出可以直接复制跑的SQL。刚接触SQL Server报表开发的读者可以把它当入门指南遇到诡异分组问题的老手也能从中找到排查思路。1. 行转列到底在解决什么问题1.1 一个经典场景成绩表从长表变宽表先看一个最典型的例子。成绩表通常这么设计CREATE TABLE StudentScore ( StudentID INT, StudentName NVARCHAR(50), SubjectName NVARCHAR(50), Score DECIMAL(5,1) ); INSERT INTO StudentScore VALUES (1, N张三, N语文, 88.5), (1, N张三, N数学, 92.0), (1, N张三, N英语, 79.5), (2, N李四, N语文, 91.0), (2, N李四, N数学, 84.0), (2, N李四, N英语, 88.0);这种存储方式符合数据库范式新增科目不用改表结构但报表端看着就是别扭。业务想要的其实是这么一张宽表StudentIDStudentName语文数学英语1张三88.592.079.52李四91.084.088.0这个过程就是把同一分组学生内的多行数据映射到同一行的多个列上也就是行转列。为什么要转因为绝大多数报表组件、Excel导出、BI仪表盘的展示逻辑都是按列读取的宽表方便直接绑定字段不用在前端再做二次透视。1.2 行转列不只是透视还有字符串拼接很多人一提行转列就只想到PIVOT其实按需求还能拆成两类解法完全不同第一类是数值聚合透视。成绩、金额、数量这类可计算的值转列后要经过SUM、MAX、AVG等聚合典型就是上面的成绩表转宽表用PIVOT或CASE WHEN做。第二类是文本值拼接。一个学生有多个联系方式一个订单有多条商品明细希望每个学生一行联系方式拼成手机:138xxxx, 邮箱:xxxxx.com这种字符串。这本质上也是行转列但PIVOT根本做不了文本拼接得用FOR XML PATH或STRING_AGG。把这两类需求分清很重要。我见过不少同学拿PIVOT去拼字符串折腾半天报错最后发现方向就错了。2. 条件聚合写法CASE WHEN MAX最稳的方案2.1 基础写法与为什么用MAX的解释条件聚合是行转列最通用、最不容易出错的写法代码长一点但逻辑完全透明SELECT StudentID, StudentName, MAX(CASE WHEN SubjectName N语文 THEN Score END) AS 语文, MAX(CASE WHEN SubjectName N数学 THEN Score END) AS 数学, MAX(CASE WHEN SubjectName N英语 THEN Score END) AS 英语 FROM StudentScore GROUP BY StudentID, StudentName;很多新手看到MAX会懵明明只是取一个成绩为什么要用聚合函数这里的原理是GROUP BY之后每个学生每个科目组内只有一条记录MAX做的就是在一组记录里挑出非NULL的那个值。如果组内有多个值用MAX还是SUM就要看业务语义了。比如同一个人同一个科目考了两次想要最高分就用MAX想要两次总分就SUM想要平均就AVG。还有一点必须注意CASE WHEN没有写ELSE时不满足条件的行返回NULL而SQL Server的聚合函数会忽略NULL。所以某个学生没考某科转出来就是NULL不是0。NULL和0在业务上含义完全不同——NULL表示没有这条记录0表示考了0分做报表时如果非要把NULL显示成0要明确业务能接受这个语义别上来就COALESCE。2.2 配合WHERE过滤和条件扩展实际业务不会只有三个科目还经常有只看期末成绩只统计已发布成绩这类过滤条件。两种处理方式一种是在外层WHERE直接过滤比如WHERE ExamType N期末聚合前就排除了其他数据另一种是在CASE里加判断MAX(CASE WHEN SubjectName N语文 AND ExamType N期末 THEN Score END)。我更推荐先过滤再聚合写进子查询或CTE里这样外层逻辑干净执行计划也更容易走索引SELECT StudentID, StudentName, MAX(CASE WHEN SubjectName N语文 THEN Score END) AS 语文, MAX(CASE WHEN SubjectName N数学 THEN Score END) AS 数学, MAX(CASE WHEN SubjectName N英语 THEN Score END) AS 英语 FROM StudentScore WHERE ExamType N期末 GROUP BY StudentID, StudentName;如果科目数量很多手写CASE WHEN确实累。但这种累是可控的因为科目名单一般变化不频繁写死反而让语句结构清晰别人接手也容易看懂。2.3 一个查询同时转出成绩、总分、平均分条件聚合最大的优势是多指标可以写在同一个SELECT里一个GROUP BY全部解决。PIVOT一次只能做一种聚合而CASE WHEN可以同时算语文最高分、数学平均分、总分SELECT StudentID, StudentName, MAX(CASE WHEN SubjectName N语文 THEN Score END) AS 语文, MAX(CASE WHEN SubjectName N数学 THEN Score END) AS 数学, MAX(CASE WHEN SubjectName N英语 THEN Score END) AS 英语, SUM(Score) AS 总分, AVG(Score) AS 平均分, SUM(CASE WHEN SubjectName IN (N语文, N数学, N英语) THEN Score END) AS 主科总分 FROM StudentScore GROUP BY StudentID, StudentName;注意最后这个主科总分如果不加CASE限制SUM(Score)会把所有科目算进去。这个细节在报表需求里非常常见——总分的定义到底是什么一定要和业务确认清楚否则数据错得无声无息。3. 官方PIVOT语法写法与两个大坑3.1 PIVOT的基本结构和执行逻辑SQL Server 2005开始内置了PIVOT运算符语义上比CASE WHEN更贴近透视这个词。成绩表转宽表的写法如下SELECT StudentID, StudentName, [语文], [数学], [英语] FROM ( SELECT StudentID, StudentName, SubjectName, Score FROM StudentScore ) AS Src PIVOT ( MAX(Score) FOR SubjectName IN ([语文], [数学], [英语]) ) AS Pvt;逐段拆解这个语法内层子查询Src定义了参与透视的数据源哪些列可以出现在外层SELECT里由它决定。PIVOT括号里第一部分MAX(Score)指定聚合函数和聚合的值列。FOR SubjectName IN (...)指定哪一列的值要变成列名IN列表里写死生成的列。最后的AS Pvt别名必须写不写直接语法报错这是SQL Server PIVOT的硬性要求。PIVOT会隐含地把内层子查询中没有出现在FOR和聚合部分的其他列当作分组依据。这个行为用好了很省事用不好就是灾难。3.2 坑一隐含分组粒度这是PIVOT最容易被坑的地方。假设内层子查询多选了一个ExamType列SELECT StudentID, StudentName, [语文], [数学], [英语] FROM ( SELECT StudentID, StudentName, SubjectName, Score, ExamType FROM StudentScore ) AS Src PIVOT ( MAX(Score) FOR SubjectName IN ([语文], [数学], [英语]) ) AS Pvt;PIVOT会认为分组依据是StudentID、StudentName、ExamType三列的组合同一个学生如果有期中、期末两条记录就会被分成两行转出来的结果出现重复行。这完全不是业务想要的。解决办法是在内层子查询里只保留三列分组列、FOR列、聚合值列。其他列想留在外层结果里要么先聚合要么在外面用子查询关联取回不要贪方便全部带上。这里给一个经验写PIVOT前先在内层子查询里做一次选列瘦身把SELECT列表压缩到最小。这能避免绝大多数PIVOT奇怪结果。3.3 坑二一次只能做一种聚合PIVOT声明里只能指定一个聚合函数和一个聚合列。业务说我要看每个学生语文数学英语的最高分还要看平均分一个PIVOT写不出来。两个常用方案方案一是先在内层子查询把数据预聚合算出需要的指标再对其中一个PIVOT方案二是在SQL Server 2005以后可以连续写多个PIVOT把第一次PIVOT的结果作为第二次的输入。但多个PIVOT嵌套读起来很费劲可维护性差。所以我的建议是单指标透视用PIVOT多指标透视直接用CASE WHEN。别为了用官方语法把简单问题复杂化。4. 动态PIVOT列不确定时的标准套路4.1 动态SQL的完整结构PIVOT的IN列表必须写死问题是科目是用户自定义的今天就语文数学英语明天可能加一门物理SQL写死就等于每周改一次代码。这时候需要动态PIVOT基本思路是三步从业务表取出去重后的科目名列表。用QUOTENAME给每个科目名加上方括号拼成[语文],[数学],[英语]这种字符串。拼出完整SELECT语句用sp_executesql动态执行。完整代码DECLARE cols NVARCHAR(MAX); SELECT cols STRING_AGG(QUOTENAME(SubjectName), ,) FROM (SELECT DISTINCT SubjectName FROM StudentScore) T; DECLARE sql NVARCHAR(MAX) N SELECT StudentID, StudentName, cols N FROM ( SELECT StudentID, StudentName, SubjectName, Score FROM StudentScore ) AS Src PIVOT ( MAX(Score) FOR SubjectName IN ( cols N) ) AS Pvt;; EXEC sp_executesql sql;cols拼出来就是[语文],[数学],[英语]直接嵌入IN列表。动态SQL的执行结果和静态PIVOT完全一样但列集合是运行时自动生成的新增科目不用改代码。4.2 取列名字符串STUFF还是STRING_AGG上面用了SQL Server 2017起才有的STRING_AGG如果你是2016或更早的版本取列名要换成经典的STUFF FOR XML PATH写法DECLARE cols NVARCHAR(MAX); SELECT cols STUFF( ( SELECT , QUOTENAME(SubjectName) FROM (SELECT DISTINCT SubjectName FROM StudentScore) T ORDER BY SubjectName FOR XML PATH() ), 1, 1, );这段的意思是把去重后的科目名按字母或笔画顺序拼接成一行每项前加逗号然后用STUFF把开头的第一个逗号替换成空字符串。STUFF的1, 1, 表示从位置1开始删除1个字符换成空串。用FOR XML PATH要注意子查询里拼字符串时会做XML转义不过这里是QUOTENAME出来的列名基本不含特殊字符影响不大。新版本能用STRING_AGG就尽量用STRING_AGG代码短一半可读性也高。4.3 安全与性能别在列名上偷懒动态SQL最让人担心的就是SQL注入。列名来源如果不可控比如用户在前端自定义了属性名那么拼SQL前必须做安全检查。两个底线要求第一列名一律用QUOTENAME包一层。QUOTENAME会正确处理包含]等特殊字符的标识符这也是防止通过列名注入的关键手段。第二列名值做白名单校验。在应用层或存储过程里用正则检查只允许中文、字母、数字、下划线长度限制合理范围。来自用户输入的标识符永远不要直接信任。性能方面动态SQL每次执行都可能生成新的执行计划因为语句文本随列名变化。如果这个报表被频繁调用编译开销不可忽视。我的做法是列集合长期稳定的情况下先用动态SQL生成一遍完整语句复制出来固化成静态SQL或存储过程只有列确实经常变化才保留动态方案。调试动态SQL时有个小技巧EXEC之前先执行PRINT sql把拼出来的语句打印到消息窗口或者改成SELECT sql查看完整文本。这么做能很快定位拼接错误别闷头直接执行。5. 字符串拼接型行转列FOR XML PATH与STRING_AGG5.1 一个学生的多个联系方式学生联系方式表CREATE TABLE StudentContact ( StudentID INT, StudentName NVARCHAR(50), ContactType NVARCHAR(20), ContactValue NVARCHAR(100) ); INSERT INTO StudentContact VALUES (1, N张三, N手机, N13812345678), (1, N张三, N邮箱, Nzhangsanexample.com), (2, N李四, N手机, N13987654321), (2, N李四, N微信, Nlishi_wechat);想要的输出是张三一行联系方式拼成手机:13812345678; 邮箱:zhangsanexample.com。这就是典型的字符串拼接型行转列。5.2 FOR XML PATH拆解2008到2016时代最通用的写法SELECT t1.StudentID, t1.StudentName, STUFF( ( SELECT ; t2.ContactType : t2.ContactValue FROM StudentContact t2 WHERE t2.StudentID t1.StudentID ORDER BY t2.ContactType FOR XML PATH() ), 1, 2, ) AS ContactList FROM StudentContact t1 GROUP BY t1.StudentID, t1.StudentName;一步步理解内层的关联子查询遍历当前学生的每一条联系方式生成一行文本; 手机:13812345678、; 邮箱:zhangsanexample.com。FOR XML PATH()不生成XML根节点把这些行直接拼成一个字符串结果是; 手机:13812345678; 邮箱:zhangsanexample.com。外层STUFF把开头第1到第2个字符也就是;两个字符替换成空串得到手机:13812345678; 邮箱:zhangsanexample.com。这里两个细节容易翻车一个是STUFF删除的字符数和分隔符长度必须一致。分隔符是;占两个字符所以STUFF参数是1, 2, 如果分隔符只用了逗号就是1, 1, 。很多人复制代码时忘了改这个2结果开头总多一个字符。另一个是外层必须有GROUP BY或DISTINCT否则每个学生的每条联系方式都会输出一行结果里全是重复学生。5.3 2017的STRING_AGG与排序控制如果数据库是SQL Server 2017以上字符串拼接有更直观的写法SELECT StudentID, StudentName, STRING_AGG(ContactType : ContactValue, ; ) WITHIN GROUP (ORDER BY ContactType) AS ContactList FROM StudentContact GROUP BY StudentID, StudentName;STRING_AGG第一个参数是要拼接的表达式第二个参数是分隔符。WITHIN GROUP (ORDER BY ContactType)控制拼接顺序这个很重要FOR XML PATH的排序写起来相对费劲STRING_AGG天生支持。有一点要提醒STRING_AGG拼接时会忽略NULL值。如果某项联系方式是NULL它不会显示任何占位内容。想让NULL显式显示成未填写需要先用ISNULL包一层。5.4 关于特殊字符转义的一个冷门坑FOR XML PATH拼字符串时有个隐蔽问题输出会经过XML转义。遇到内容里有、、这些字符结果会变成lt;、gt;、amp;。比如联系方式里有个备注家庭电话备用拼出来就成了家庭电话lt;备用gt;。如果下游程序把这串文本直接当普通字符串用就会出现莫名其妙的乱码。STRING_AGG不会做XML转义识别到内容可能含特殊字符时优先用STRING_AGG。这也是很多老项目升级到2017后逐步用STRING_AGG替换FOR XML PATH的根本原因——不是性能问题是转义语义问题。6. 常见问题速查与选型经验6.1 常见问题速查表现象可能原因处理方式转出来的列全是NULL透视值不匹配或内层分组粒度过细检查数据源有没有对应记录内层子查询只保留必要列结果出现重复行PIVOT隐含分组混入了多余列内层SELECT只选分组列FOR列值列动态SQL报列名无效cols为空或列名含特殊字符没加方括号用QUOTENAME包裹列名执行前PRINT sql检查拼接结果出现lt;等字符FOR XML PATH做了XML转义改用STRING_AGG2017或提前替换特殊字符中文列名报语法错误列名在IN列表里没有加方括号中文列名统一加[]报表频繁执行时性能变差动态SQL每次重编译或者源表缺少索引列稳定就固化SQL给透视列和分组列建索引这六类问题基本覆盖了我日常排障中八成以上的行转列故障。遇到异常结果时别急着改语法先分别单独执行内层子查询和最终SELECT往往能快速定位是数据问题还是分组问题。6.2 性能对比与执行计划观察很多人纠结PIVOT和CASE WHEN哪个快实际在SQL Server里PIVOT最终也会被优化成类似条件聚合加分组聚合的执行计划性能差别微乎其微。真正的差异来源是这几点条件聚合是一个清晰的聚合算子执行计划一般是一条Stream Aggregate或Hash Match Aggregate源表扫描一次。静态PIVOT同样能看到聚合算子只是表达式结构更复杂一些。动态PIVOT额外多了取列名查询和字符串拼接核心聚合计划和静态版一致但要关注每次动态编译的CPU开销。FOR XML PATH的关联子查询可能走Nested Loops数据量大时内层查询被反复执行这时候要给关联列比如StudentID建索引或者直接用STRING_AGG。说白了行转列的性能瓶颈通常不在转这个动作而在源表的扫描和分组聚合本身。把透视列和分组列建好索引比纠结选哪种语法更实际。6.3 我的选型习惯和一个小技巧做行转列需求做了这么多年我给自己定了一条简单的选型规则列固定且只有单指标优先用PIVOT语义直观代码短列固定但有多指标用CASE WHEN一个GROUP BY能同时输出一堆指标列不固定才用动态PIVOT加QUOTENAME并且严格做白名单校验如果是字符串拼接2017以上一律用STRING_AGG2016以下只能FOR XML PATH。每个方案都有适用边界没有银弹。PIVOT虽然名字专业但隐含分组这个特性太容易让维护者踩坑CASE WHEN啰嗦但逻辑透明排查问题省心。我自己在正式项目里七成以上的行转列都是CASE WHEN写出来的。最后分享一个调试技巧。写动态SQL时习惯性在EXEC前面先执行一句PRINT sql把拼好的语句打印到SSMS消息窗口。如果语句太长被截断就把PRINT改成SELECT sql点击结果列把全部文本复制出来贴到新查询窗口格式化后再看。这样排查动态拼接错误比反复改代码快得多。做行转列前先把分组维度是什么、透视维度是什么、聚合值是什么这三个问题问清楚方案基本就定了。