嘎嘣豆儿 發表於 2025-12-13 11:21:50

SQL Server 中的表进行行转列场景示例

<div id="navCategory"><h5 class="catalogue">目录</h5><ul class="first_class_ul"><li><a href="#_label0">一、常见场景示例</a></li><li><a href="#_label1">二、写法 1：PIVOT（固定列名）</a></li><li><a href="#_label2">三、写法 2：条件聚合（CASE WHEN）</a></li><li><a href="#_label3">四、写法 3：动态列名（Dynamic PIVOT）</a></li><ul class="second_class_ul"><li><a href="#_lab2_3_0">4.1 适用于 SQL Server 2017+（STRING_AGG）</a></li><li><a href="#_lab2_3_1">4.2 适用于 SQL Server 2016 及更早（FOR XML PATH）</a></li></ul><li><a href="#_label4">五、反向操作：列转行（UNPIVOT或UNION ALL）</a></li><ul class="second_class_ul"><li><a href="#_lab2_4_2">5.1 使用UNPIVOT</a></li><li><a href="#_lab2_4_3">5.2 使用UNION ALL（更直观、可控）</a></li></ul><li><a href="#_label5">六、常见进阶需求</a></li><ul class="second_class_ul"><li><a href="#_lab2_5_4">6.1 小计/合计</a></li><li><a href="#_lab2_5_5">6.2 按月/季度/年展开为列</a></li><li><a href="#_lab2_5_6">6.3 多指标同时透视</a></li></ul><li><a href="#_label6">七、性能与索引建议</a></li><ul class="second_class_ul"></ul><li><a href="#_label7">八、可直接替换的最简模板</a></li><ul class="second_class_ul"><li><a href="#_lab2_7_7">固定列（PIVOT）</a></li><li><a href="#_lab2_7_8">条件聚合</a></li><li><a href="#_lab2_7_9">动态列（2017+）</a></li></ul></ul></div><p>下面给你一份 <strong>SQL Server 行转列（Pivot）</strong> 的全攻略，包含三种常用写法、完整示例、动态列数处理、性能与易踩坑点。你可以直接复制粘贴模板改表名/字段名即可。</p>
<p class="maodian"><a name="_label0"></a></p><h2>一、常见场景示例</h2>
<p>假设原始表 <code>Sales</code> 结构如下：</p>
<div class="jb51code"><pre class="brush:sql;">CREATE TABLE Sales (
    SalesDate date,
    Region    nvarchar(50),
    Product   nvarchar(50),
    Qty       int
);
-- 示例数据
INSERT INTO Sales VALUES
('2025-01-01', 'North', 'A', 10),
('2025-01-01', 'North', 'B', 20),
('2025-01-01', 'South', 'A', 15),
('2025-01-01', 'South', 'B', 5),
('2025-01-02', 'North', 'A', 8),
('2025-01-02', 'South', 'B', 12);</pre></div>
<p>目标：将 <code>Product</code> 的不同值（A、B&hellip;）变成列，数值填 <code>SUM(Qty)</code>，行按 <code>SalesDate</code>、<code>Region</code>。</p>
<p class="maodian"><a name="_label1"></a></p><h2>二、写法 1：PIVOT（固定列名）</h2>
<p>当你 <strong>已知列集合</strong>（比如只有 <code>A/B/C</code>）时，<code>PIVOT</code> 是最直观的：</p>
<div class="jb51code"><pre class="brush:sql;">SELECT SalesDate, Region, ISNULL(, 0) AS A, ISNULL(, 0) AS B
FROM (
    SELECT SalesDate, Region, Product, Qty
    FROM Sales
) AS src
PIVOT (
    SUM(Qty) FOR Product IN (, )
) AS p
ORDER BY SalesDate, Region;</pre></div>
<p><strong>要点</strong></p>
<ul><li><code>FOR Product IN (, )</code> 中必须写死列名。</li><li>聚合函数可用 <code>SUM/COUNT/MAX...</code>。</li><li>若存在 <code>NULL</code>，可用 <code>ISNULL</code> 补 0。</li><li>多指标（比如 <code>SUM(Qty)</code> 与 <code>COUNT(*)</code> 同时）可用两次 PIVOT 或用条件聚合（见写法 2）。</li></ul>
<p class="maodian"><a name="_label2"></a></p><h2>三、写法 2：条件聚合（CASE WHEN）</h2>
<p>当你想 <strong>灵活控制计算逻辑</strong> 或 <strong>一次输出多个指标</strong>，推荐条件聚合：</p>
<div class="jb51code"><pre class="brush:sql;">SELECT
    SalesDate,
    Region,
    SUM(CASE WHEN Product = 'A' THEN Qty ELSE 0 END) AS A,
    SUM(CASE WHEN Product = 'B' THEN Qty ELSE 0 END) AS B,
    COUNT(CASE WHEN Product = 'A' THEN 1 END)       AS A_cnt,
    COUNT(CASE WHEN Product = 'B' THEN 1 END)       AS B_cnt
FROM Sales
GROUP BY SalesDate, Region
ORDER BY SalesDate, Region;</pre></div>
<p><strong>优点</strong></p>
<ul><li>不需要 <code>PIVOT</code> 语法，语义清晰、可读性强。</li><li>可以在同一查询里输出多种计算指标（数量、金额、最大值&hellip;）。</li><li>与窗口函数/更多条件结合更自然。</li></ul>
<p><strong>缺点</strong></p>
<ul><li>列集合仍需&ldquo;写死&rdquo;。需要动态列时见写法 3。</li></ul>
<p class="maodian"><a name="_label3"></a></p><h2>四、写法 3：动态列名（Dynamic PIVOT）</h2>
<p>当 <strong>列值不固定</strong>（例如产品会新增），需要 <strong>动态构造</strong> 列清单。SQL Server 一般用 <code>STRING_AGG</code>（SQL 2017+）或 <code>FOR XML PATH</code> 生成列清单，再拼接动态 SQL。</p>
<p class="maodian"><a name="_lab2_3_0"></a></p><h3>4.1 适用于 SQL Server 2017+（STRING_AGG）</h3>
<div class="jb51code"><pre class="brush:sql;">DECLARE @cols nvarchar(max);
DECLARE @sqlnvarchar(max);
-- 1) 动态列清单（加方括号并去重、排序）
SELECT @cols = STRING_AGG(QUOTENAME(Product), ',')
FROM (SELECT DISTINCT Product FROM Sales) d;
-- 2) 组装动态 SQL
SET @sql = N'
SELECT SalesDate, Region, ' + @cols + N'
FROM (
    SELECT SalesDate, Region, Product, Qty
    FROM Sales
) AS src
PIVOT (
    SUM(Qty) FOR Product IN (' + @cols + N')
) p
ORDER BY SalesDate, Region;';
-- 3) 执行
EXEC sp_executesql @sql;
``</pre></div>
<p class="maodian"><a name="_lab2_3_1"></a></p><h3>4.2 适用于 SQL Server 2016 及更早（FOR XML PATH）</h3>
<div class="jb51code"><pre class="brush:sql;">DECLARE @cols nvarchar(max) = N'';
DECLARE @sqlnvarchar(max);
SELECT @cols = STUFF((
    SELECT ',' + QUOTENAME(Product)
    FROM (SELECT DISTINCT Product FROM Sales) d
    FOR XML PATH(''), TYPE
).value('.', 'nvarchar(max)'), 1, 1, '');
SET @sql = N'
SELECT SalesDate, Region, ' + @cols + N'
FROM (
    SELECT SalesDate, Region, Product, Qty
    FROM Sales
) AS src
PIVOT (
    SUM(Qty) FOR Product IN (' + @cols + N')
) p
ORDER BY SalesDate, Region;';
EXEC sp_executesql @sql;
``</pre></div>
<p><strong>注意</strong></p>
<ul><li><code>QUOTENAME</code> 用来安全地给列名加 <code>[]</code>，避免特殊字符出错。</li><li>动态 SQL 结果集列名在编译期未知，若要在上层程序接收，通常需要固定列或使用临时表/表变量承接。</li><li>若列很多（上百上千），请同时考虑客户端呈现是否可读。</li></ul>
<p class="maodian"><a name="_label4"></a></p><h2>五、反向操作：列转行（UNPIVOT或UNION ALL）</h2>
<p>如果你有宽表（多列）要转成长表：</p>
<p class="maodian"><a name="_lab2_4_2"></a></p><h3>5.1 使用UNPIVOT</h3>
<div class="jb51code"><pre class="brush:sql;">SELECT SalesDate, Region, Product, Qty
FROM (
    SELECT SalesDate, Region, ,
    FROM PivotedSales
) p
UNPIVOT (
    Qty FOR Product IN (, )
) AS u;
``</pre></div>
<p class="maodian"><a name="_lab2_4_3"></a></p><h3>5.2 使用UNION ALL（更直观、可控）</h3>
<div class="jb51code"><pre class="brush:sql;">SELECT SalesDate, Region, 'A' AS Product, A AS Qty FROM PivotedSales
UNION ALL
SELECT SalesDate, Region, 'B', B FROM PivotedSales;</pre></div>
<p class="maodian"><a name="_label5"></a></p><h2>六、常见进阶需求</h2>
<p class="maodian"><a name="_lab2_5_4"></a></p><h3>6.1 小计/合计</h3>
<div class="jb51code"><pre class="brush:sql;">-- 在行转列之前做汇总，再 PIVOT
WITH agg AS (
    SELECT SalesDate, Region, Product, SUM(Qty) AS Qty
    FROM Sales
    GROUP BY SalesDate, Region, Product
)
SELECT *
FROM agg
PIVOT (SUM(Qty) FOR Product IN (,)) p
UNION ALL
-- 合计行
SELECT SalesDate, 'Total' AS Region, ,
FROM (
    SELECT SalesDate, Product, SUM(Qty) Qty
    FROM Sales
    GROUP BY SalesDate, Product
) s
PIVOT (SUM(Qty) FOR Product IN (,)) p
ORDER BY SalesDate, CASE WHEN Region='Total' THEN 1 ELSE 0 END, Region;
``</pre></div>
<p class="maodian"><a name="_lab2_5_5"></a></p><h3>6.2 按月/季度/年展开为列</h3>
<div class="jb51code"><pre class="brush:sql;">SELECT Region,
       SUM(CASE WHEN FORMAT(SalesDate,'yyyy-MM') = '2025-01' THEN Qty ELSE 0 END) AS ,
       SUM(CASE WHEN FORMAT(SalesDate,'yyyy-MM') = '2025-02' THEN Qty ELSE 0 END) AS
FROM Sales
GROUP BY Region;</pre></div>
<blockquote><p>更高性能可用 <code>DATEFROMPARTS/YEAR/MONTH</code> + 字符拼接代替 <code>FORMAT</code>（<code>FORMAT</code> 对大表较慢）。</p></blockquote>
<p class="maodian"><a name="_lab2_5_6"></a></p><h3>6.3 多指标同时透视</h3>
<div class="jb51code"><pre class="brush:sql;">SELECT
    SalesDate,
    Region,
    SUM(CASE WHEN Product='A' THEN Qty END) AS A_qty,
    COUNT(CASE WHEN Product='A' THEN 1 END) AS A_cnt,
    SUM(CASE WHEN Product='B' THEN Qty END) AS B_qty,
    COUNT(CASE WHEN Product='B' THEN 1 END) AS B_cnt
FROM Sales
GROUP BY SalesDate, Region;
``</pre></div>
<p class="maodian"><a name="_label6"></a></p><h2>七、性能与索引建议</h2>
<ol><li><strong>先聚合再透视</strong>：对大表务必先 <code>GROUP BY</code> 汇总，再 <code>PIVOT</code>，能显著减少数据量。</li><li><strong>适配索引</strong>：<ul><li>行转列通常按（行维度列 + 列维度列）聚合，如示例按 <code>SalesDate, Region, Product</code>。</li><li>可以考虑覆盖索引：<div class="jb51code"><pre class="brush:sql;">CREATE INDEX IX_Sales_Pivot
ON Sales (SalesDate, Region, Product)
INCLUDE (Qty);
</pre></div></li></ul></li><li><strong>避免函数包装索引列</strong>：例如在谓词里用 <code>FORMAT(SalesDate, ...)</code> 会导致索引失效，改用 <code>SalesDate &gt;= @d1 AND SalesDate &lt; @d2</code>。</li><li><strong>控制列数量</strong>：输出列过多会影响网络传输与结果集处理；必要时分页或拆查询。</li><li><strong>NULL 处理</strong>：<code>PIVOT</code> 得到 <code>NULL</code> 很常见，展示前用 <code>ISNULL/COALESCE</code>。</li><li><strong>权限与安全</strong>：动态 SQL 用 <code>QUOTENAME</code> 防止注入；尽量不要直接拼接来自用户输入的列名/表名。</li></ol>
<p class="maodian"><a name="_label7"></a></p><h2>八、可直接替换的最简模板</h2>
<p class="maodian"><a name="_lab2_7_7"></a></p><h3>固定列（PIVOT）</h3>
<div class="jb51code"><pre class="brush:sql;">SELECT 维度列1, 维度列2, ISNULL([列值1],0) AS 列值1, ISNULL([列值2],0) AS 列值2
FROM (
    SELECT 维度列1, 维度列2, 列名来源列, 度量列
    FROM 源表
) s
PIVOT (
    聚合函数(度量列) FOR 列名来源列 IN ([列值1],[列值2])
) p;</pre></div>
<p class="maodian"><a name="_lab2_7_8"></a></p><h3>条件聚合</h3>
<div class="jb51code"><pre class="brush:sql;">SELECT 维度列1, 维度列2,
       SUM(CASE WHEN 列名来源列='列值1' THEN 度量列 ELSE 0 END) AS 列值1,
       SUM(CASE WHEN 列名来源列='列值2' THEN 度量列 ELSE 0 END) AS 列值2
FROM 源表
GROUP BY 维度列1, 维度列2;</pre></div>
<p class="maodian"><a name="_lab2_7_9"></a></p><h3>动态列（2017+）</h3>
<div class="jb51code"><pre class="brush:sql;">DECLARE @cols nvarchar(max), @sql nvarchar(max);
SELECT @cols = STRING_AGG(QUOTENAME(列名来源列), ',')
FROM (SELECT DISTINCT 列名来源列 FROM 源表) d;
SET @sql = N'
SELECT 维度列1, 维度列2, ' + @cols + N'
FROM (SELECT 维度列1, 维度列2, 列名来源列, 度量列 FROM 源表) s
PIVOT (聚合函数(度量列) FOR 列名来源列 IN (' + @cols + N')) p;';
EXEC sp_executesql @sql;</pre></div>
頁: [1]
查看完整版本: SQL Server 中的表进行行转列场景示例