SqlServer 中行转列PIVOT函数用法
<h1 id="sqlserver-中行转列pivot函数用法">SqlServer 中行转列PIVOT函数用法</h1><h2 id="前言">前言</h2>
<p>最近在面试的时候，碰到了手写<code>sql</code>的题目，这让我这个面向AI的程序员着实难看。只见我面露难色，绞尽脑汁的情况下，终于还是放弃了。</p>
<p>这道题目不难，但是由于平时几乎没有遇到行转列的情况，导致在手写时忘记了<code>PIVOT</code>函数怎么使用😩。</p>
<p><em><font color="gray" size="2">面试准备不充分给自己找借口，菜就多练，不会写就别写。</font></em></p>
<h2 id="题目描述">题目描述</h2>
<p>下面请看题：</p>
<p>假设有以下表 <code>EmpCanlendar</code>：</p>
<table>
<thead>
<tr>
<th style="text-align: left">Name</th>
<th style="text-align: left">CalendarDate</th>
<th style="text-align: left">ClassName</th>
</tr>
</thead>
<tbody>
<tr>
<td style="text-align: left">张三</td>
<td style="text-align: left">2005-05-01</td>
<td style="text-align: left">日班</td>
</tr>
<tr>
<td style="text-align: left">张三</td>
<td style="text-align: left">2005-05-02</td>
<td style="text-align: left">日班</td>
</tr>
<tr>
<td style="text-align: left">张三</td>
<td style="text-align: left">2005-05-03</td>
<td style="text-align: left">夜班</td>
</tr>
<tr>
<td style="text-align: left">李四</td>
<td style="text-align: left">2005-05-01</td>
<td style="text-align: left">夜班</td>
</tr>
<tr>
<td style="text-align: left">李四</td>
<td style="text-align: left">2005-05-02</td>
<td style="text-align: left">日班</td>
</tr>
<tr>
<td style="text-align: left">...</td>
<td style="text-align: left">...</td>
<td style="text-align: left">...</td>
</tr>
</tbody>
</table>
<p><strong>输出结果：</strong></p>
<table>
<thead>
<tr>
<th style="text-align: left">Name</th>
<th style="text-align: left">D20050501</th>
<th style="text-align: left">D20050502</th>
<th style="text-align: left">D20050503</th>
<th style="text-align: left">D20050504</th>
<th style="text-align: left">D20050505</th>
</tr>
</thead>
<tbody>
<tr>
<td style="text-align: left">张三</td>
<td style="text-align: left">日班</td>
<td style="text-align: left">日班</td>
<td style="text-align: left">日班</td>
<td style="text-align: left">日班</td>
<td style="text-align: left">夜班</td>
</tr>
<tr>
<td style="text-align: left">李四</td>
<td style="text-align: left">日班</td>
<td style="text-align: left">日班</td>
<td style="text-align: left">日班</td>
<td style="text-align: left">NULL</td>
<td style="text-align: left">NULL</td>
</tr>
<tr>
<td style="text-align: left">王五</td>
<td style="text-align: left">NULL</td>
<td style="text-align: left">夜班</td>
<td style="text-align: left">夜班</td>
<td style="text-align: left">NULL</td>
<td style="text-align: left">NULL</td>
</tr>
</tbody>
</table>
<h2 id="pivot函数简单介绍"><code>PIVOT</code>函数简单介绍</h2>
<blockquote>
<p><code>PIVOT</code> 是 SQL Server 中的一种功能，用于将行数据转换为列数据（即行转列）。它通常用于将某一列的唯一值作为新列，并将对应的值填充到这些新列中。<code>PIVOT</code> 是数据透视表的一种实现方式，非常适合用于统计和报表场景。</p>
</blockquote>
<h3 id="pivot-的基本语法"><code>PIVOT</code> 的基本语法</h3>
<pre><code class="language-sql">SELECT
[非透视列],
[透视值1], [透视值2], ..., [透视值N]
FROM
(
-- 子查询：提供原始数据
SELECT [非透视列], [透视列], [值列]
FROM 表名
) AS 源表
PIVOT
(
聚合函数(值列) -- 例如 SUM、COUNT、MAX 等
FOR 透视列 IN ([透视值1], [透视值2], ..., [透视值N])
) AS 透视表
</code></pre>
<h3 id="参数">参数</h3>
<ol>
<li><strong>非透视列</strong>：
<ul>
<li>不需要转换的列，这些列的值将作为结果表的行标识。</li>
</ul>
</li>
<li><strong>透视列</strong>：
<ul>
<li>需要转换为新列的列。</li>
</ul>
</li>
<li><strong>值列</strong>：
<ul>
<li>需要填充到新列中的值。</li>
</ul>
</li>
<li><strong>聚合函数</strong>：
<ul>
<li>对值列进行聚合操作，例如 <code>SUM</code>、<code>COUNT</code>、<code>MAX</code> 等。</li>
<li>如果值列不需要聚合，可以使用 <code>MAX</code> 或 <code>MIN</code>。</li>
</ul>
</li>
<li><strong>透视值</strong>：
<ul>
<li>透视列中的唯一值，这些值将成为新列的名称。</li>
</ul>
</li>
</ol>
<h2 id="解题">解题</h2>
<h3 id="测试数据准备">测试数据准备</h3>
<pre><code class="language-sql">CREATE TABLE #EmpCanlendar(
NVARCHAR(20) NULL,
CalendarDate DATETIME NULL,
ClassName NVARCHAR(10) NULL,
)
INSERT INTO #EmpCanlendar(Name,CalendarDate,ClassName)
VALUES
('张三','2005-05-01','日班'),
('张三','2005-05-02','日班'),
('张三','2005-05-03','日班'),
('张三','2005-05-04','日班'),
('张三','2005-05-05','夜班'),
('李四','2005-05-01','日班'),
('李四','2005-05-02','日班'),
('李四','2005-05-03','日班'),
('王五','2005-05-02','夜班'),
('王五','2005-05-03','夜班')
</code></pre>
<h3 id="非动态sql">非动态<code>sql</code></h3>
<p>适用于日期范围固定，或者日期列较少的情况</p>
<pre><code class="language-sql">SELECT
Name,
AS D20050501,
AS D20050502,
AS D20050503,
AS D20050504,
AS D20050505
FROM
(
SELECT
Name,
CalendarDate,
ClassName
FROM
#EmpCanlendar
) AS SourceTable
PIVOT
(
MAX(ClassName)
FOR CalendarDate IN (, , , , )
) AS PivotTable;
</code></pre>
<p><strong>查询结果</strong></p>
<p><img src="https://img2024.cnblogs.com/blog/3091176/202503/3091176-20250320204511886-1208577374.png" alt="image" loading="lazy"></p>
<h3 id="动态sql">动态<code>sql</code></h3>
<p>适用于日期范围不固定，或者日期列较多的情况</p>
<p><strong>SQL Server 2017</strong>及以上版本：</p>
<pre><code class="language-sql">-- 声明变量存储动态 SQL
DECLARE @sql NVARCHAR(MAX) = ''
DECLARE @columns NVARCHAR(MAX) = ''
SELECT @columns = STRING_AGG(QUOTENAME('D' + CONVERT(VARCHAR, DistinctDates.CalendarDate, 112)), ',')
FROM (SELECT DISTINCT CalendarDate FROM #EmpCanlendar) AS DistinctDates
SET @sql = '
SELECT
Name , ' + @columns +'
FROM
(
SELECT
Name,
''D'' + CONVERT(VARCHAR,CalendarDate, 112) CalendarDate,
ClassName
FROM
#EmpCanlendar
) AS SourceTable
PIVOT
(
MAX(ClassName)
FOR CalendarDate IN ('+ @columns +')
) AS PivotTable;
'
PRINT @sql
EXEC sp_executesql @sql
</code></pre>
<p><strong>SQL Server 2017</strong>以下版本</p>
<pre><code class="language-sql">SELECT @columns = STUFF(
(
SELECT DISTINCT ',' + QUOTENAME('D'+ CONVERT(VARCHAR, CalendarDate, 112))
FROM #EmpCanlendar
FOR XML PATH('')
),1, 1, ''
)
</code></pre>
<p><strong>查询结果：</strong></p>
<p><img src="https://img2024.cnblogs.com/blog/3091176/202503/3091176-20250320204518661-392298767.png" alt="image" loading="lazy"></p>
<h2 id="扩展">扩展</h2>
<h3 id="stuff函数简单介绍"><code>STUFF</code>函数简单介绍</h3>
<p><code>STUFF</code> 是 SQL Server 中的一个字符串函数，用于删除字符串的一部分并在指定位置插入新的子字符串。它的主要作用是修改字符串的内容，通常用于拼接或替换字符串中的某些部分。</p>
<h4 id="stuff-函数的语法"><code>STUFF</code> 函数的语法：</h4>
<pre><code class="language-sql">STUFF(原始字符串, 开始位置, 删除长度, 新子字符串)
</code></pre>
<h4 id="参数-1">参数</h4>
<ol>
<li><strong>原始字符串</strong>：
<ul>
<li>需要修改的字符串。</li>
<li>可以是字符型（<code>CHAR</code>、<code>VARCHAR</code>、<code>NVARCHAR</code> 等）的列、变量或表达式。</li>
</ul>
</li>
<li><strong>开始位置</strong>：
<ul>
<li>指定从原始字符串的哪个位置开始删除和插入。</li>
<li>位置从 1 开始计数。</li>
</ul>
</li>
<li><strong>删除长度</strong>：
<ul>
<li>指定要删除的字符数。</li>
<li>如果为 0，则不删除任何字符，仅插入新子字符串。</li>
<li>如果大于原始字符串的长度，则从开始位置删除到字符串末尾。</li>
</ul>
</li>
<li><strong>新子字符串</strong>：
<ul>
<li>要插入的新字符串。</li>
<li>如果为空字符串 <code>''</code>，则仅删除字符，不插入新内容。</li>
</ul>
</li>
</ol>
<h4 id="可用于">可用于</h4>
<ul>
<li><strong>删除字符</strong>：从指定位置开始删除一定长度的字符。</li>
<li><strong>插入字符</strong>：在删除字符的位置插入新的子字符串。</li>
<li><strong>替换字符</strong>：通过删除和插入操作，可以实现字符串的替换。</li>
</ul>
<h3 id="string_agg函数简单介绍"><code>STRING_AGG</code>函数简单介绍：</h3>
<p><code>STRING_AGG</code> 是 SQL Server 2017 及更高版本中引入的一个聚合函数，用于将一组字符串值连接成一个单独的字符串，并使用指定的分隔符分隔每个值。它是 SQL Server 中处理字符串拼接的强大工具，特别适合将多行数据合并为一个字符串。</p>
<h4 id="string_agg-的基本语法"><code>STRING_AGG</code> 的基本语法</h4>
<pre><code class="language-sql">STRING_AGG(表达式, 分隔符)
</code></pre>
<h4 id="参数-2">参数</h4>
<ol>
<li><strong>表达式</strong>：
<ul>
<li>需要拼接的字符串列或表达式。</li>
<li>可以是 <code>VARCHAR</code>、<code>NVARCHAR</code>、<code>CHAR</code> 等字符类型。</li>
</ul>
</li>
<li><strong>分隔符</strong>：
<ul>
<li>用于分隔每个字符串值的字符或字符串。</li>
<li>可以是任意字符串，例如 <code>,</code>、<code>;</code>、<code>-</code> 等。</li>
</ul>
</li>
</ol>
<h3 id="quotename函数简单介绍"><code>QUOTENAME</code>函数简单介绍</h3>
<p>在SQL Server中，<code>QUOTENAME()</code>函数用于将一个标识符（如表名、列名等）包围在方括号中，以防止引起语法错误或与关键字冲突。</p>
<h4 id="quotename函数的语法"><code>QUOTENAME</code>函数的语法：</h4>
<pre><code class="language-Sql">QUOTENAME ( 'character_string' [ , 'quote_character' ] )
</code></pre>
<h4 id="参数-3">参数</h4>
<p><code>'character_string'</code>：是要包围的标识符，可以是表名、列名等</p>
<p><code>'quote_character'</code>：是可选参数，用于指定用于包围标识符的字符，默认为方括号（[ ]）。</p>
<h2 id="参考链接">参考链接</h2>
<ul>
<li>深入浅出：<code>SQL Server</code> 中的 <code>PIVOT</code> 与 <code>UNPIVOT</code> 用法详解：https://blog.csdn.net/houbincarson/article/details/145483265</li>
<li><code>QUOTENAME (Transact-SQL)</code> ：https://learn.microsoft.com/zh-cn/sql/t-sql/functions/quotename-transact-sql?view=sql-server-ver16</li>
<li><code>SQL Server</code>中<code>quotename(</code>)函数怎么使用：https://www.cnblogs.com/luyj00436/p/18453443</li>
</ul><br><br>
来源：https://www.cnblogs.com/ZYPLJ/p/18783932
頁:
[1]