SQL Server中的PIVOT与UNPIVOT的使用方法及示例代码详解

在数据分析和报表生成场景中，经常需要将行数据转换为列数据（PIVOT）或将列数据转换为行数据（UNPIVOT）。SQL Server 2005+ 版本原生支持这两种操作，本文ZHANID工具网将通过原理剖析、语法详解和实战案例，系统讲解其使用方法。

一、核心概念解析

1. PIVOT（旋转）

定义：将行数据转换为列数据，通常需要结合聚合函数使用。
典型场景：

• 生成交叉报表（如各地区季度销售额）

• 将时间维度从行转为列（如月份展开）

• 实现类似Excel数据透视表的功能

2. UNPIVOT（逆旋转）

定义：将列数据转换为行数据，恢复规范化数据结构。
典型场景：

• 数据清洗（将宽表转为长表）

• 兼容需要固定列数的旧系统

• 简化复杂查询条件

二、PIVOT语法详解

基础语法模板

SELECT <非透视列>, [第一列], [第二列], ... 
FROM 
(
    SELECT <要透视的列>, <聚合列>, <非透视列>
    FROM 表名
) AS 源数据
PIVOT 
(
    聚合函数(<聚合列>)
    FOR <要透视的列> IN ([第一列], [第二列], ...)
) AS 透视结果

关键参数说明

1. 非透视列：保留在结果集中的原始列

2. 要透视的列：需要转为列标题的字段

3. 聚合列：参与聚合计算的数值字段

4. 聚合函数：通常使用SUM/AVG/COUNT等

三、UNPIVOT语法详解

基础语法模板

SELECT <原列>, <新列>, <值列>
FROM 表名
UNPIVOT 
(
    <值列> FOR <新列> IN ([列1], [列2], ...)
) AS 逆透视结果

关键参数说明

1. 原列：保留在结果集中的原始列

2. 新列：存储原列标题的新字段

3. 值列：存储原列值的字段

四、实战案例解析

案例1：PIVOT基础应用（销售数据透视）

原始数据（Sales表）：

目标：生成各地区季度销售额透视表
实现代码：

SELECT 
    Region,
    [Q1], [Q2]
FROM 
(
    SELECT Region, Quarter, Amount 
    FROM Sales
) AS SourceData
PIVOT 
(
    SUM(Amount)
    FOR Quarter IN ([Q1], [Q2])
) AS PivotTable

输出结果：

案例2：UNPIVOT基础应用（数据规范化）

原始数据（PivotSales表）：

目标：恢复原始行结构
实现代码：

SELECT 
    Region,
    Quarter,
    Amount
FROM 
(
    SELECT Region, Q1, Q2 
    FROM PivotSales
) AS SourceData
UNPIVOT 
(
    Amount FOR Quarter IN (Q1, Q2)
) AS UnpivotTable

输出结果：

五、高级应用技巧

1. 动态PIVOT（处理未知列）

当需要透视的列值不固定时（如未知年份），需使用动态SQL：

DECLARE @columns NVARCHAR(MAX), @sql NVARCHAR(MAX)

SELECT @columns = STRING_AGG(QUOTENAME(Quarter), ',') 
FROM (SELECT DISTINCT Quarter FROM Sales) AS Q

SET @sql = '
SELECT Region, ' + @columns + '
FROM 
(
    SELECT Region, Quarter, Amount 
    FROM Sales
) AS SourceData
PIVOT 
(
    SUM(Amount)
    FOR Quarter IN (' + @columns + ')
) AS PivotTable'

EXEC sp_executesql @sql

2. 多列PIVOT（复合透视）

同时透视多个字段的技巧：

SELECT 
    Region,
    [Q1_Sales], [Q2_Sales],
    [Q1_Orders], [Q2_Orders]
FROM 
(
    SELECT Region, Quarter, 
           SUM(Amount) AS Sales,
           COUNT(*) AS Orders
    FROM Sales
    GROUP BY Region, Quarter
) AS SourceData
PIVOT 
(
    MAX(Sales)
    FOR Quarter IN ([Q1_Sales], [Q2_Sales])
) AS P1
PIVOT 
(
    MAX(Orders)
    FOR Quarter IN ([Q1_Orders], [Q2_Orders])
) AS P2

3. 条件UNPIVOT（过滤数据）

仅转换特定列的技巧：

SELECT 
    Region,
    Quarter,
    Amount
FROM 
(
    SELECT Region, Q1, Q2 
    FROM PivotSales
) AS SourceData
UNPIVOT 
(
    Amount FOR Quarter IN (Q1, Q2)
) AS UnpivotTable
WHERE Amount > 1600

六、常见问题解决

1. 处理NULL值

PIVOT操作后可能出现NULL，使用COALESCE处理：

SELECT 
    Region,
    COALESCE([Q3], 0) AS Q3
FROM ...

2. 性能优化建议

• 对大数据量表，优先使用条件聚合代替PIVOT

• 为透视列建立索引

• 避免在临时表上使用PIVOT

3. 数据类型一致性

UNPIVOT要求所有参与列具有相同数据类型，否则需显式转换：

UNPIVOT 
(
    Amount FOR Quarter IN 
    (
        CAST(Q1 AS DECIMAL(10,2)), 
        CAST(Q2 AS DECIMAL(10,2))
    )
)

七、替代方案对比

八、最佳实践指南

1. 优先使用PIVOT：当列值范围已知且固定时

2. 谨慎使用动态PIVOT：必须做好参数验证防止SQL注入

3. UNPIVOT前数据清洗：确保所有列数据类型一致

4. 索引优化：为透视列和聚合列建立合适索引

5. 结果集限制：对大数据集使用TOP子句限制结果数量

九、总结

PIVOT和UNPIVOT是SQL Server中强大的数据转换工具，适用于数据透视、报表生成等场景。掌握其使用方法可以：

• 简化复杂查询逻辑

• 提升代码可读性

• 快速实现Excel式数据透视功能

但需注意其局限性：

• PIVOT必须结合聚合函数使用

• 动态列处理需要额外技术

• 大数据量下可能影响性能

合理选择使用场景，结合条件聚合等替代方案，可以构建高效稳定的数据处理流程。实际开发中建议通过SQL Profiler监控执行计划，持续优化查询性能。