SQL Server中的PIVOT与UNPIVOT用法具体示例详解_MsSqlserver

引言

在数据分析与报表生成场景中，行列转换是一个高频需求。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预处理、存储优化	将多个月份列合并为日期维度