SqlServer 中行转列与列转行

admin

2026年1月18日 13:38 本文热度 6

一、常见场景示例

假设原始表 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...。
若存在 NULL，可用 ISNULL 补 0。
多指标（比如 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;

优点

不需要 PIVOT 语法，语义清晰、可读性强。
可以在同一查询里输出多种计算指标（数量、金额、最大值…）。
与窗口函数/更多条件结合更自然。

缺点

列集合仍需“写死”。需要动态列时见写法 3。

四、写法 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);
-- 1) 动态列清单（加方括号并去重、排序）SELECT @cols = STRING_AGG(QUOTENAME(Product), ',')FROM (SELECT DISTINCT Product FROM Sales) d;
-- 2) 组装动态 SQLSET @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;';
-- 3) 执行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 ALL-- 合计行SELECT 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 编辑过

关键字查询

Server

正在查询...