SQL Server 表行转列方法详解

要点

• FOR Product IN ([A], [B]) 中必须写死列名。
• 聚合函数可用 SUM/COUNT/MAX...。
• 若存在 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_cnt 
FROM Sales 
GROUP BY SalesDate, Region 
ORDER 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) 组装动态 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;

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 src 
    PIVOT (
        SUM(Qty) FOR Product IN (' + @cols + N') 
    ) p 
    ORDER BY SalesDate, Region;';

EXEC sp_executesql @sql;

注意

• QUOTENAME 用来安全地给列名加 []，避免特殊字符出错。
• 动态 SQL 结果集列名在编译期未知，若要在上层程序接收，通常需要固定列或使用临时表/表变量承接。
• 若列很多（上百上千），请同时考虑客户端呈现是否可读。

五、反向操作：列转行（UNPIVOT 或 UNION ALL）

如果你有宽表（多列）要转成长表：

5.1 使用 UNPIVOT

SELECT SalesDate, Region, Product, Qty 
FROM (
    SELECT SalesDate, Region, [A], [B] FROM PivotedSales 
) p 
UNPIVOT (
    Qty FOR Product IN ([A], [B]) 
) AS u;

5.2 使用 UNION ALL（更直观、可控）

SELECT SalesDate, Region, 'A' AS Product, A AS Qty FROM PivotedSales 
UNION ALL 
SELECT SalesDate, Region, 'B', B FROM PivotedSales;

六、常见进阶需求

6.1 小计/合计

-- 在行转列之前做汇总，再 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;

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 Sales 
GROUP 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_cnt 
FROM Sales 
GROUP BY SalesDate, Region;

七、性能与索引建议

1. 先聚合再透视：对大表务必先 GROUP BY 汇总，再 PIVOT，能显著减少数据量。
2. 适配索引：
   • 行转列通常按（行维度列 + 列维度列）聚合，如示例按 SalesDate, Region, Product。
3. 避免函数包装索引列：例如在谓词里用 FORMAT(SalesDate, ...) 会导致索引失效，改用 SalesDate >= @d1 AND SalesDate < @d2。
4. 控制列数量：输出列过多会影响网络传输与结果集处理；必要时分页或拆查询。
5. NULL 处理：PIVOT 得到 NULL 很常见，展示前用 ISNULL/COALESCE。
6. 权限与安全：动态 SQL 用 QUOTENAME 防止注入；尽量不要直接拼接来自用户输入的列名/表名。

可以考虑覆盖索引：

CREATE INDEX IX_Sales_Pivot ON Sales (SalesDate, Region, Product) INCLUDE (Qty); 

八、可直接替换的最简模板

固定列（PIVOT）

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;

动态列（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 源表
    ) s 
    PIVOT (
        聚合函数 (度量列) FOR 列名来源列 IN (' + @cols + N')
    ) p;';
EXEC sp_executesql @sql;