详细介绍 SQL Server 中实现表行转列的三种方案：固定列名的 PIVOT 语法、条件聚合（CASE WHEN）及动态 SQL 生成。涵盖列转行（UNPIVOT）、小计合计、多指标透视等进阶需求，并提供性能优化与索引建议。通过具体示例代码，帮助开发者解决宽表与长表转换问题，确保查询效率与数据安全。
假设原始表 Sales 结构如下：
CREATE TABLE Sales (
SalesDate ,
Region nvarchar(),
Product nvarchar(),
Qty
);
Sales
(, , , ),
(, , , ),
(, , , ),
(, , , ),
(, , , ),
(, , , );
目标：将 Product 的不同值（A、B…）变成列，数值填 SUM(Qty)，行按 SalesDate、Region。
当你 已知列集合（比如只有 A/B/C）时，PIVOT 是最直观的：
SELECT
SalesDate,
Region,
ISNULL([A], 0) AS A,
ISNULL([B], 0) AS B
FROM (
SELECT SalesDate, Region, Product, Qty FROM Sales
) AS src
PIVOT (
SUM(Qty) FOR Product IN ([A], [B])
) AS p
ORDER BY SalesDate, Region;
要点
FOR Product IN ([A], [B]) 中必须写死列名。SUM/COUNT/MAX...。NULL，可用 ISNULL 补 0。SUM(Qty) 与 COUNT(*) 同时）可用两次 PIVOT 或用条件聚合（见写法 2）。当你想 灵活控制计算逻辑 或 一次输出多个指标，推荐条件聚合：
SELECT
SalesDate,
Region,
SUM(CASE WHEN Product = 'A' THEN Qty ELSE 0 END) AS A,
SUM(CASE WHEN Product = 'B' THEN Qty ELSE 0 END) AS B,
COUNT(CASE WHEN Product = 'A' THEN 1 END) AS A_cnt,
COUNT(CASE WHEN Product = 'B' THEN 1 END) AS B_cnt
FROM Sales
GROUP BY SalesDate, Region
ORDER BY SalesDate, Region;
优点
PIVOT 语法，语义清晰、可读性强。缺点
当 列值不固定（例如产品会新增），需要 动态构造 列清单。SQL Server 一般用 STRING_AGG（SQL 2017+）或 FOR XML PATH 生成列清单，再拼接动态 SQL。
DECLARE @cols nvarchar(max);
DECLARE @sql nvarchar(max);
-- 1) 动态列清单（加方括号并去重、排序）
SELECT @cols = STRING_AGG(QUOTENAME(Product), ',')
FROM (SELECT DISTINCT Product FROM Sales) d;
-- 2) 组装动态 SQL
SET @sql = N'
SELECT SalesDate, Region, ' + @cols + N'
FROM (
SELECT SalesDate, Region, Product, Qty FROM Sales
) AS src
PIVOT (
SUM(Qty) FOR Product IN (' + @cols + N')
) p
ORDER BY SalesDate, Region;';
-- 3) 执行
EXEC sp_executesql @sql;
DECLARE @cols nvarchar(max) = N'';
DECLARE @sql nvarchar(max);
SELECT @cols = STUFF((
SELECT ',' + QUOTENAME(Product)
FROM (SELECT DISTINCT Product FROM Sales) d
FOR XML PATH(''), TYPE
).value('.', 'nvarchar(max)'), 1, 1, '');
SET @sql = N'
SELECT SalesDate, Region, ' + @cols + N'
FROM (
SELECT SalesDate, Region, Product, Qty FROM Sales
) AS src
PIVOT (
SUM(Qty) FOR Product IN (' + @cols + N')
) p
ORDER BY SalesDate, Region;';
EXEC sp_executesql @sql;
注意
QUOTENAME 用来安全地给列名加 []，避免特殊字符出错。如果你有宽表（多列）要转成长表：
SELECT SalesDate, Region, Product, Qty
FROM (
SELECT SalesDate, Region, [A], [B] FROM PivotedSales
) p
UNPIVOT (
Qty FOR Product IN ([A], [B])
) AS u;
SELECT SalesDate, Region, 'A' AS Product, A AS Qty FROM PivotedSales
UNION ALL
SELECT SalesDate, Region, 'B', B FROM PivotedSales;
-- 在行转列之前做汇总，再 PIVOT
WITH agg AS (
SELECT SalesDate, Region, Product, SUM(Qty) AS Qty
FROM Sales
GROUP BY SalesDate, Region, Product
)
SELECT * FROM agg
PIVOT (SUM(Qty) FOR Product IN ([A],[B])) p
UNION ALL
-- 合计行
SELECT SalesDate, 'Total' AS Region, [A], [B]
FROM (
SELECT SalesDate, Product, SUM(Qty) Qty
FROM Sales
GROUP BY SalesDate, Product
) s
PIVOT (SUM(Qty) FOR Product IN ([A],[B])) p
ORDER BY SalesDate, CASE WHEN Region='Total' THEN 1 ELSE 0 END, Region;
SELECT
Region,
SUM(CASE WHEN FORMAT(SalesDate,'yyyy-MM') = '2025-01' THEN Qty ELSE 0 END) AS [2025-01],
SUM(CASE WHEN FORMAT(SalesDate,'yyyy-MM') = '2025-02' THEN Qty ELSE 0 END) AS [2025-02]
FROM Sales
GROUP BY Region;
更高性能可用
DATEFROMPARTS/YEAR/MONTH+ 字符拼接代替FORMAT（FORMAT对大表较慢）。
SELECT
SalesDate,
Region,
SUM(CASE WHEN Product='A' THEN Qty END) AS A_qty,
COUNT(CASE WHEN Product='A' THEN 1 END) AS A_cnt,
SUM(CASE WHEN Product='B' THEN Qty END) AS B_qty,
COUNT(CASE WHEN Product='B' THEN 1 END) AS B_cnt
FROM Sales
GROUP BY SalesDate, Region;
GROUP BY 汇总，再 PIVOT，能显著减少数据量。SalesDate, Region, Product。FORMAT(SalesDate, ...) 会导致索引失效，改用 SalesDate >= @d1 AND SalesDate < @d2。PIVOT 得到 NULL 很常见，展示前用 ISNULL/COALESCE。QUOTENAME 防止注入；尽量不要直接拼接来自用户输入的列名/表名。可以考虑覆盖索引：
CREATE INDEX IX_Sales_Pivot ON Sales (SalesDate, Region, Product) INCLUDE (Qty);
SELECT
维度列 1,
维度列 2,
ISNULL([列值 1],0) AS 列值 1,
ISNULL([列值 2],0) AS 列值 2
FROM (
SELECT 维度列 1, 维度列 2, 列名来源列，度量列 FROM 源表
) s
PIVOT (
聚合函数 (度量列) FOR 列名来源列 IN ([列值 1],[列值 2])
) p;
SELECT
维度列 1,
维度列 2,
SUM(CASE WHEN 列名来源列='列值 1' THEN 度量列 ELSE 0 END) AS 列值 1,
SUM(CASE WHEN 列名来源列='列值 2' THEN 度量列 ELSE 0 END) AS 列值 2
FROM 源表
GROUP BY 维度列 1, 维度列 2;
DECLARE @cols nvarchar(max), @sql nvarchar(max);
SELECT @cols = STRING_AGG(QUOTENAME(列名来源列), ',')
FROM (SELECT DISTINCT 列名来源列 FROM 源表) d;
SET @sql = N'
SELECT 维度列 1, 维度列 2, ' + @cols + N'
FROM (
SELECT 维度列 1, 维度列 2, 列名来源列，度量列 FROM 源表
) s
PIVOT (
聚合函数 (度量列) FOR 列名来源列 IN (' + @cols + N')
) p;';
EXEC sp_executesql @sql;
微信公众号「极客日志」，在微信中扫描左侧二维码关注。展示文案：极客日志 zeeklog
在线格式化和美化您的 SQL 查询（它支持各种 SQL 方言）。 在线工具，SQL 美化和格式化在线工具，online
解析 INSERT 等受限 SQL，导出为 CSV、JSON、XML、YAML、HTML 表格（见页内语法说明）。 在线工具，SQL转CSV/JSON/XML在线工具，online
CSV 与 JSON/XML/HTML/TSV/SQL 等互转，单页多 Tab。 在线工具，CSV 工具包在线工具，online
将字符串编码和解码为其 Base64 格式表示形式即可。 在线工具，Base64 字符串编码/解码在线工具，online
将字符串、文件或图像转换为其 Base64 表示形式。 在线工具，Base64 文件转换器在线工具，online
将 Markdown（GFM）转为 HTML 片段，浏览器内 marked 解析；与 HTML转Markdown 互为补充。 在线工具，Markdown转HTML在线工具，online