我要评论
引言
在数据分析与报表生成场景中，行列转换是一个高频需求。sql server 提供了 pivot 和 unpivot 两个强大的运算符，能够帮助我们快速实现数据透视与逆透视操作。本文将结合具体示例，解析它们的核心用法。
一、pivot：将行转换为列
pivot函数主要是用来将数据从行转换成列。比如，如果有订单数据表，里面有很多订单的信息，可能按客户id、订单日期等分组。使用pivot可以把这些重复的客户信息排列成一个更紧凑的表格，每个客户的订单日期变成一列，这样看起来更直观。
核心作用
将某一列的唯一值作为新列名，并按需聚合关联数据。
语法结构
select [非透视列], [透视列1], [透视列2], ...
from (
select [列1], [列2], [聚合列]
from 表
) as 源表
pivot (
聚合函数(聚合列)
for [目标列] in ([透视值1], [透视值2], ...)
) as 别名;
实战示例
场景：统计各部门在不同季度的销售额。
- 准备数据
create table #sales (
department varchar(50),
quarter char(2),
amount decimal(10,2)
);
insert into #sales values
('hr', 'q1', 20000),
('hr', 'q2', 22000),
('it', 'q1', 35000),
('it', 'q3', 41000);
- 执行 pivot
select department, [q1], [q2], [q3], [q4]
from (
select department, quarter, amount
from #sales
) as src
pivot (
sum(amount)
for quarter in ([q1], [q2], [q3], [q4])
) as pvt;
输出结果：
二、unpivot：将列转换为行
unpivot函数，它的作用和pivot相反，是用来把数据从列转换回行。比如，在pivot之后得到的一张表格里，如果需要进一步细分数据或者进行其他操作，可以用unpivot来恢复原来的多行结构。
核心作用
将多列合并为两列（属性名+属性值），实现数据逆向透视。
语法结构
select [非透视列], [属性列], [值列]
from 表
unpivot (
值列 for 属性列 in ([列1], [列2], ...)
) as 别名;
实战示例
场景：将季度销售额列还原为行结构。
- 使用之前 pivot 的结果作为输入
create table #pivotedsales (
department varchar(50),
q1 decimal(10,2),
q2 decimal(10,2),
q3 decimal(10,2),
q4 decimal(10,2)
);
insert into #pivotedsales values
('hr', 20000, 22000, null, null),
('it', 35000, null, 41000, null);
- 执行 unpivot
select department, quarter, amount
from #pivotedsales
unpivot (
amount for quarter in (q1, q2, q3, q4)
) as unpvt;
输出结果：
三、关键注意事项
数据类型一致性unpivot 的所有列必须具有兼容的数据类型。
处理 null 值pivot 会自动过滤 null 值，可通过
isnull()或coalesce()预处理。动态列处理当透视列值不固定时，需使用动态 sql 拼接列名（示例需另写代码实现）。
性能优化对大型数据集建议建立合适索引，避免全表扫描。
四、典型应用场景对比
| 操作 | 适用场景 | 示例 |
|---|---|---|
| pivot | 生成交叉报表、统计类报表 | 部门季度销售汇总 |
| unpivot | 数据规范化、etl预处理、存储优化 | 将多个月份列合并为日期维度 |
五、总结
- pivot 通过聚合实现行转列，适合制作汇总视图
- unpivot 通过逆向操作恢复数据结构，适合数据清洗
- 二者配合使用可完成复杂数据转换需求
到此这篇关于sql server中的pivot与unpivot用法具体示例的文章就介绍到这了,更多相关sqlserver pivot与unpivot用法内容请搜索代码网以前的文章或继续浏览下面的相关文章希望大家以后多多支持代码网！
发表评论