SQL Server 中的表进行行转列场景示例

本文主要是介绍SQL Server 中的表进行行转列场景示例，希望对大家解决编程问题提供一定的参考价值，需要的开发者们随着小编来一起学习吧！

《SQLServer中的表进行行转列场景示例》本文详细介绍了SQLServer行转列（Pivot）的三种常用写法,包括固定列名、条件聚合和动态列名,文章还提供了实际示例、动态列数处理、性能优化建议...

一、常见场景示例

假设原始表 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', http://www.chinasem.cn'B', 5),
javascript('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 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）。

三、写法 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 SalesDaChina编程te, 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
GROU编程P BY Region;

更高性能可用 DATEFROMPARTS/YEAR/MONTH + 字符拼接代替 FORMAT（FORMAT 对大表较慢）。

6.3 多指标同时透视

SELECT
    SalesDate,
    Region,
    SUM(CASE WHEN Product='A' THEN Qty END) AS A_qty,
    COU编程NT(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。
- 可以考虑覆盖索引：
```
CREATE INDEX IX_Sales_Pivot
ON Sales (SalesDate, Region, Product)
INCLUDE (Qty);
```
避免函数包装索引列：例如在谓词里用 FORMAT(SalesDate, ...) 会导致索引失效，改用 SalesDate >= @d1 AND SalesDate < @d2。
控制列数量：输出列过多会影响网络传输与结果集处理；必要时分页或拆查询。
NULL 处理：PIVOT 得到 NULL 很常见，展示前用 ISNULL/COALESCE。
权限与安全：动态 SQL 用 QUOTENAME 防止注入；尽量不要直接拼接来自用户输入的列名/表名。

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

固定列（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;

到此这篇关于SQL Server 中的表进行行转列场景示例的文章就介绍到这了,更多相关sqlserver行转列内容请搜索编程China编程(www.chinasem.cn)以前的文章或继续浏览下面的相关文章希望大家以后多多支持China编程(www.chinasem.cn)！

这篇关于SQL Server 中的表进行行转列场景示例的文章就介绍到这儿，希望我们推荐的文章对编程师们有所帮助！

SQL Server 中的表进行行转列场景示例

目录

一、常见场景示例

二、写法 1：PIVOT（固定列名）

三、写法 2：条件聚合（CASE WHEN）

四、写法 3：动态列名（Dynamic PIVOT）

4.1 适用于 SQL Server 2017+（STRING_AGG）

4.2 适用于 SQL Server 2016 及更早（FOR XML PATH）

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

5.1 使用UNPIVOT

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

六、常见进阶需求

6.1 小计/合计

6.2 按月/季度/年展开为列

6.3 多指标同时透视

七、性能与索引建议

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

固定列（PIVOT）

条件聚合

动态列（2017+）

相关文章

Python在二进制文件中进行数据搜索的实战指南

利用c++判断水仙花数并输出示例代码

Java 接口定义变量的示例代码

Mybatis对MySQL if 函数的不支持问题解读

Java中的CompletableFuture核心用法和常见场景

MySQL 筛选条件放 ON后 vs 放 WHERE 后的区别解析

mysql_mcp_server部署及应用实践案例

Mysql中RelayLog中继日志的使用

使用Redis实现会话管理的示例代码

MySQL日志UndoLog的作用