企业官网建设流程全解析

在SQL Server里做报表、做数据透视，绕不开的一个操作就是行转列。比如你有一张销售流水表，每个季度一行数据，老板非要把四个季度变成四列，一行一个年份，这种需求每周都能遇到几次。网上搜“SQL Server 行转列”能搜出一堆代码，但很多是片段，照着抄完还不一定对，尤其是动态列那块，报错报得莫名其妙。

这篇文章就把行转列这事讲透。你会看到最常见的三种做法：PIVOT、CASE WHEN聚合、动态SQL拼接，以及它们的适用场景和坑。我还会把生产环境里踩过的雷、排查思路一起写出来，附上可以直接跑的SQL脚本。无论你用的是SQL Server 2008 R2还是2022，只要数据库是Microsoft SQL Server系列，这套逻辑基本通用，适合刚接触透视表查询的开发者，也适合想从“会抄”提升到“会写”的运维和数据分析同学。

1. 先搞明白：什么样子的数据需要行转列

1.1 一个最常见的业务场景

先看数据。假设我们有一张年度销售表，结构非常简单：年份、季度、销售额。

这是典型的“长表”结构，一行表示一个季度。但报表需求通常长这样：横轴是季度，纵轴是年份，每个季度一列，一眼能看到2022年四个季度的对比。

把数据行变成结果集的列，这就是“行转列”。这个操作在关系数据库里没有一个叫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); GO

2.2 PIVOT核心语法与执行逻辑

固定四个季度，最直接的写法就是PIVOT：

SELECT 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不匹配时返回NULL，SUM会忽略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);

然后做UNPIVOT：

SELECT 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是DECIMAL，Q4是INT，SQL 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再JOIN：

SELECT 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 WHEN，Gathering数据时都能走索引覆盖，扫描成本低不少。

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的脾气摸透。