快乐的搬运工 發表於 2025-5-17 10:57:57

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

<div id="navCategory"><h5 class="catalogue">目录</h5><ul class="first_class_ul"><li><a href="#_label0">引言</a></li><li><a href="#_label1">一、PIVOT：将行转换为列</a></li><ul class="second_class_ul"><li><a href="#_lab2_1_0">核心作用</a></li><li><a href="#_lab2_1_1">语法结构</a></li><li><a href="#_lab2_1_2">实战示例</a></li></ul><li><a href="#_label2">二、UNPIVOT：将列转换为行</a></li><ul class="second_class_ul"><li><a href="#_lab2_2_3">核心作用</a></li><li><a href="#_lab2_2_4">语法结构</a></li><li><a href="#_lab2_2_5">实战示例</a></li></ul><li><a href="#_label3">三、关键注意事项</a></li><ul class="second_class_ul"></ul><li><a href="#_label4">四、典型应用场景对比</a></li><ul class="second_class_ul"></ul><li><a href="#_label5">五、总结</a></li><ul class="second_class_ul"></ul></ul></div><p style="text-align:center"><img alt="" src="https://img.jbzj.com/file_images/article/202505/2025051710553811.png" /></p>
<p class="maodian"><a name="_label0"></a></p><h2>引言</h2>
<p>在数据分析与报表生成场景中，<strong>行列转换</strong>是一个高频需求。SQL Server 提供了&nbsp;<code>PIVOT</code>&nbsp;和&nbsp;<code>UNPIVOT</code>&nbsp;两个强大的运算符，能够帮助我们快速实现数据透视与逆透视操作。本文将结合具体示例，解析它们的核心用法。</p>
<p class="maodian"><a name="_label1"></a></p><h2>一、PIVOT：将行转换为列</h2>
<p>PIVOT函数主要是用来将数据从行转换成列。比如，如果有订单数据表，里面有很多订单的信息，可能按客户ID、订单日期等分组。使用PIVOT可以把这些重复的客户信息排列成一个更紧凑的表格，每个客户的订单日期变成一列，这样看起来更直观。</p>
<p class="maodian"><a name="_lab2_1_0"></a></p><p class="maodian"><a name="_lab2_2_3"></a></p><h3>核心作用</h3>
<p>将某一列的唯一值作为新列名，并按需聚合关联数据。</p>
<p class="maodian"><a name="_lab2_1_1"></a></p><p class="maodian"><a name="_lab2_2_4"></a></p><h3>语法结构</h3>
<div class="jb51code"><pre class="brush:sql;">SELECT [非透视列], [透视列1], [透视列2], ...
FROM (
    SELECT [列1], [列2], [聚合列]
    FROM 表
) AS 源表
PIVOT (
    聚合函数(聚合列)
    FOR [目标列] IN ([透视值1], [透视值2], ...)
) AS 别名;
</pre></div>
<p class="maodian"><a name="_lab2_1_2"></a></p><p class="maodian"><a name="_lab2_2_5"></a></p><h3>实战示例</h3>
<p><strong>场景</strong>：统计各部门在不同季度的销售额。</p>
<ul><li><strong>准备数据</strong></li></ul>
<div class="jb51code"><pre class="brush:sql;">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);
</pre></div>
<ul><li><strong>执行 PIVOT</strong></li></ul>
<div class="jb51code"><pre class="brush:sql;">SELECT Department, , , ,
FROM (
    SELECT Department, Quarter, Amount
    FROM #Sales
) AS Src
PIVOT (
    SUM(Amount)
    FOR Quarter IN (, , , )
) AS Pvt;
</pre></div>
<p><strong>输出结果</strong>：</p>
<p style="text-align:center"><img alt="" src="https://img.jbzj.com/file_images/article/202505/2025051710553812.png" /></p>
<p class="maodian"><a name="_label2"></a></p><h2>二、UNPIVOT：将列转换为行</h2>
<p>UNPIVOT函数，它的作用和PIVOT相反，是用来把数据从列转换回行。比如，在PIVOT之后得到的一张表格里，如果需要进一步细分数据或者进行其他操作，可以用UNPIVOT来恢复原来的多行结构。</p>
<h3>核心作用</h3>
<p>将多列合并为两列（属性名+属性值），实现数据逆向透视。</p>
<h3>语法结构</h3>
<div class="jb51code"><pre class="brush:sql;">SELECT [非透视列], [属性列], [值列]
FROM 表
UNPIVOT (
    值列 FOR 属性列 IN ([列1], [列2], ...)
) AS 别名;
</pre></div>
<h3>实战示例</h3>
<p><strong>场景</strong>：将季度销售额列还原为行结构。</p>
<ul><li><strong>使用之前 PIVOT 的结果作为输入</strong></li></ul>
<div class="jb51code"><pre class="brush:sql;">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);
</pre></div>
<ul><li><strong>执行 UNPIVOT</strong></li></ul>
<div class="jb51code"><pre class="brush:sql;">SELECT Department, Quarter, Amount
FROM #PivotedSales
UNPIVOT (
    Amount FOR Quarter IN (Q1, Q2, Q3, Q4)
) AS Unpvt;
</pre></div>
<p><strong>输出结果</strong>：</p>
<p style="text-align:center"><img alt="" src="https://img.jbzj.com/file_images/article/202505/2025051710553913.png" /></p>
<p class="maodian"><a name="_label3"></a></p><h2>三、关键注意事项</h2>
<ul><li><p><strong>数据类型一致性</strong>UNPIVOT 的所有列必须具有兼容的数据类型。</p></li><li><p><strong>处理 NULL 值</strong>PIVOT 会自动过滤 NULL 值，可通过&nbsp;<code>ISNULL()</code>&nbsp;或&nbsp;<code>COALESCE()</code>&nbsp;预处理。</p></li><li><p><strong>动态列处理</strong>当透视列值不固定时，需使用动态 SQL 拼接列名（示例需另写代码实现）。</p></li><li><p><strong>性能优化</strong>对大型数据集建议建立合适索引，避免全表扫描。</p></li></ul>
<p class="maodian"><a name="_label4"></a></p><h2>四、典型应用场景对比</h2>
<table><tbody><tr><th>操作</th><th>适用场景</th><th>示例</th></tr><tr><td>PIVOT</td><td>生成交叉报表、统计类报表</td><td>部门季度销售汇总</td></tr><tr><td>UNPIVOT</td><td>数据规范化、ETL预处理、存储优化</td><td>将多个月份列合并为日期维度</td></tr></tbody></table>
<p class="maodian"><a name="_label5"></a></p><h2>五、总结</h2>
<ul><li><strong>PIVOT</strong>&nbsp;通过聚合实现行转列，适合制作汇总视图</li><li><strong>UNPIVOT</strong>&nbsp;通过逆向操作恢复数据结构，适合数据清洗</li><li>二者配合使用可完成复杂数据转换需求</li></ul>
頁: [1]
查看完整版本: SQL Server中的PIVOT与UNPIVOT用法具体示例详解