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

下面给你一份 sql server 行转列（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', '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 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 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;
``

七、性能与索引建议

先聚合再透视：对大表务必先 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行转列内容请搜索代码网以前的文章或继续浏览下面的相关文章希望大家以后多多支持代码网！