一、常见场景示例
假设原始表 Sales 结构如下：
CREATE TABLE Sales ( SalesDate date, Region nvarchar(50), Product nvarchar(50), Qty int);
-- 示例数据INSERT INTO Sales VALUES('2025-01-01', 'North', 'A', 10),('2025-01-01', 'North', 'B', 20),('2025-01-01', 'South', 'A', 15),('2025-01-01', 'South', 'B', 5),('2025-01-02', 'North', 'A', 8),('2025-01-02', 'South', 'B', 12);
目标：将 Product 的不同值（A、B…）变成列，数值填 SUM(Qty)，行按 SalesDate、Region。
二、写法 1：PIVOT（固定列名）
当你 已知列集合（比如只有 A/B/C）时，PIVOT 是最直观的：
SELECT SalesDate, Region, ISNULL([A], 0) AS A, ISNULL([B], 0) AS BFROM ( SELECT SalesDate, Region, Product, Qty FROM Sales) AS srcPIVOT ( SUM(Qty) FOR Product IN ([A], [B])) AS pORDER BY SalesDate, Region;
要点
FOR Product IN ([A], [B])- 聚合函数可用
SUM/COUNT/MAX/MIN...。 - 多指标（比如
SUM(Qty) 与 COUNT(*) 同时）可用两次 PIVOT 或用条件聚合（见写法 2）。
三、写法 2：条件聚合（CASE WHEN）
当你想 灵活控制计算逻辑 或 一次输出多个指标，推荐条件聚合：
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_cntFROM SalesGROUP BY SalesDate, RegionORDER BY SalesDate, Region;
优点
- 可以在同一查询里输出多种计算指标（数量、金额、最大值…）。
缺点
四、写法 3：动态列名（Dynamic PIVOT）
当 列值不固定（例如产品会新增），需要 动态构造 列清单。SQL Server一般用 STRING_AGG（SQL 2017+）或 FOR XML PATH 生成列清单，再拼接动态SQL
4.1 适用于 SQL Server 2017+（STRING_AGG）
DECLARE @cols nvarchar(max);DECLARE @sql nvarchar(max);
SELECT @cols = STRING_AGG(QUOTENAME(Product), ',')FROM (SELECT DISTINCT Product FROM Sales) d;
SET @sql = N'SELECT SalesDate, Region, ' + @cols + N'FROM ( SELECT SalesDate, Region, Product, Qty FROM Sales) AS srcPIVOT ( SUM(Qty) FOR Product IN (' + @cols + N')) pORDER BY SalesDate, Region;';
EXEC sp_executesql @sql;
4.2 适用于 SQL Server 2016 及更早（FOR XML PATH）
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 srcPIVOT ( SUM(Qty) FOR Product IN (' + @cols + N')) pORDER BY SalesDate, Region;';
EXEC sp_executesql @sql;
注意
QUOTENAME- 动态 SQL 结果集列名在编译期未知，若要在上层程序接收，通常需要固定列或使用临时表/表变量承接。
- 若列很多（上百上千），请同时考虑客户端呈现是否可读。
五、反向操作：列转行（UNPIVOT 或 UNION ALL）
如果你有宽表（多列）要转成长表：
5.1 使用 UNPIVOT
SELECT SalesDate, Region, Product, QtyFROM ( SELECT SalesDate, Region, [A], [B] FROM PivotedSales) pUNPIVOT ( Qty FOR Product IN ([A], [B])) AS u;
5.2 使用 UNION ALL（更直观、可控）
SELECT SalesDate, Region, 'A' AS Product, A AS Qty FROM PivotedSalesUNION ALLSELECT SalesDate, Region, 'B', B FROM PivotedSales
六、常见进阶需求
6.1 小计/合计
WITH agg AS ( SELECT SalesDate, Region, Product, SUM(Qty) AS Qty FROM Sales GROUP BY SalesDate, Region, Product)SELECT *FROM aggPIVOT (SUM(Qty) FOR Product IN ([A],[B])) pUNION ALLSELECT SalesDate, 'Total' AS Region, [A], [B]FROM ( SELECT SalesDate, Product, SUM(Qty) Qty FROM Sales GROUP BY SalesDate, Product) sPIVOT (SUM(Qty) FOR Product IN ([A],[B])) pORDER BY SalesDate, CASE WHEN Region='Total' THEN 1 ELSE 0 END, Region
6.2 按月/季度/年展开为列
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 SalesGROUP BY Region;
更高性能可用 DATEFROMPARTS/YEAR/MONTH + 字符拼接代替 FORMAT（FORMAT 对大表较慢）。
6.3 多指标同时透视
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_cntFROM SalesGROUP BY SalesDate, Region;
七、性能与索引建议
- 先聚合再透视：对大表务必先
GROUP BY 汇总，再 PIVOT，能显著减少数据量。 - 适配索引：
- 行转列通常按（行维度列 + 列维度列）聚合，如示例按
SalesDate, Region, Product。
CREATE INDEX IX_Sales_PivotON Sales (SalesDate, Region, Product)INCLUDE (Qty);
3.避免函数包装索引列：例如在谓词里用 FORMAT(SalesDate, ...) 会导致索引失效，改用 SalesDate >= @d1 AND SalesDate < @d2。
4.控制列数量：输出列过多会影响网络传输与结果集处理；必要时分页或拆查询。
5.NULL 处理：PIVOT 得到 NULL 很常见，展示前用 ISNULL/COALESCE。
6.权限与安全：动态 SQL 用 QUOTENAME 防止注入；尽量不要直接拼接来自用户输入的列名/表名。
八、可直接替换的最简模板
固定列（PIVOT）
SELECT 维度列1, 维度列2, ISNULL([列值1],0) AS 列值1, ISNULL([列值2],0) AS 列值2FROM ( SELECT 维度列1, 维度列2, 列名来源列, 度量列 FROM 源表) sPIVOT ( 聚合函数(度量列) 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 列值2FROM 源表GROUP BY 维度列1, 维度列2
动态列（2017+）
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 源表) sPIVOT (聚合函数(度量列) FOR 列名来源列 IN (' + @cols + N')) p;';
EXEC sp_executesql @sql
阅读原文：原文链接
该文章在 2026/1/19 10:57:09 编辑过
400 186 1886
