SQL Server使用PIVOT与UNPIVOT实现行列转换
在 SQL Server 中，行列转换是数据展示常见的操作需求。本文详细讲解如何使用 PIVOT 算子将多行记录合并为一行（行转列），以及使用 UNPIVOT 算子将宽表拆分为长表（列转行）。内容涵盖基础固定列语法与动态 SQL 适应多变列场景的完整步骤。
一、使用 PIVOT 进行行转列
PIVOT 运算符主要用于旋转表值表达式，将某一列的唯一值转换为结果集中的多个列。在实际应用中，通常需要配合聚合函数（如 COUNT、SUM 等）使用。
第1步：创建示例数据并准备环境
首先需要创建一个包含基础数据的临时表，用于演示行列转换的效果。这里建立一个名为 #table1 的临时表，包含标识列 ID、代码列 Code 和名称列 Name。
create table #table1
( id int ,code varchar(10) , name varchar(20) );
go
insert into #table1 ( id,code, name ) values ( 1, 'm1','a' ), ( 2, 'm2',null ), ( 3, 'm3', 'c' ), ( 4, 'm2','d' ), ( 5, 'm1','c' );
go
select * from #table1;
第2步：执行 PIVOT 固定列转换
对于已知的列名集合，可以直接在 IN 子句中指定。以下 SQL 语句统计每种 name 对应的 id 数量，并将不同的 name 值作为独立的列显示。
--方法一（推荐）
select PVT.code, PVT.a, PVT.b, PVT.c
from #table1 pivot(count(id) for name in(a, b, c)) as PVT;
--方法二：使用 CTE 增强可读性
with P as (select * from #table1)
select PVT.code, PVT.a, PVT.b, PVT.c
from P pivot(count(id) for name in(a, b, c)) as PVT;
第3步：观察转换结果
执行上述查询后，原始数据中的 “a”、“b”、“c” 等具体行数据会被转置为表头的列名，每行对应不同的 code 值及其各列的计数统计。
第4步：处理动态列（传统方式与动态 SQL）
如果目标行值是不确定的或频繁变化的，硬编码 IN (a, b, c) 会导致维护困难。此时可以先查询出要转为列的行数据，再拼接成动态字符串执行转列。
declare @strCN nvarchar(100);
-- 先查询出要转为列的行数据，拼接列名字符串
select @strCN = isnull(@strCN + ',', '') + quotename(name) from #table1 group by name ;
print @strCN --‘[a],[c],[d]'
declare @SqlStr nvarchar(1000);
set @SqlStr = N'
select * from #table1 pivot ( count(ID) for name in (' + @strCN + N') ) as PVT';
exec ( @SqlStr );
二、使用 UNPIVOT 进行列转行
与 PIVOT 相反，UNPIVOT 运算符将表的列旋转为行数据。这常用于将宽表还原为标准的关系型长表格式，通常出现在数据仓库清洗场景中。
第5步：创建逆向结构的测试表
构建一个拥有多列相同业务含义数据（如 name1, name2, name3）的表 #table1，其中 ID 为主键区分不同记录。
create table #table1 (id int,
code varchar(10),
name1 varchar(20),
name2 varchar(20),
name3 varchar(20));
go
insert into #table1(id, name1, name2, code, name3)
values(1, 'm1', 'a1', 'a2', 'a3'),
(2, 'm2', 'b1', 'b2', 'b3'),
(4, 'm1', 'c1', 'c2', 'c3');
go
select * from #table1;第6步：执行 UNPIVOT 还原数据
使用 UNPIVOT 语法将 name1、name2、name3 这三列的数据拉直，分别生成新表的 name（原列名）和 val（原列值）两列。
--方法一：直接使用源表
select PVT.id, PVT.code, PVT.name, PVT.val
from #table1 unpivot(val for name in(name1, name2, name3)) as PVT;
--方法二：结合 CTE 书写
with P as (select * from #table1)
select PVT.id, PVT.code, PVT.name, PVT.val
from P unpivot(val for name in(name1, name2, name3)) as PVT;
第7步：核对展开后的数据形态
转换完成后，原来的每一行会依据需要处理的列数生成多条记录，原本分散在不同水平方向的属性值统一归集到了一列中。
三、简要总结
掌握 PIVOT 和 UNPIVOT 能够极大简化复杂报表的开发过程。在开发时，若列项固定优先使用标准语法以保证性能；面对未知的动态字段范围，则应提前拼接好带方括号的字段列表并通过动态 SQL 执行。以上就是 SQL Server 使用 PIVOT 与 UNPIVOT 实现行列转换的详细内容，更多关于 T-SQL 数据处理技巧的资料请关注本站其它相关文章！
免责声明：本文内容仅供参考，不代表本站观点。转载或引用请注明出处。
