SQL Server 中的表進(jìn)行行轉(zhuǎn)列場(chǎng)景示例
更新時(shí)間：2025年12月13日 11:21:50 作者：衡水世耀科技有限公司
本文詳細(xì)介紹了SQL Server行轉(zhuǎn)列（Pivot）的三種常用寫法,包括固定列名、條件聚合和動(dòng)態(tài)列名,文章還提供了實(shí)際示例、動(dòng)態(tài)列數(shù)處理、性能優(yōu)化建議以及反向操作（列轉(zhuǎn)行）的示例,此外,還討論了常見進(jìn)階需求和性能與索引建議,感興趣的朋友跟隨小編一起看看吧
下面給你一份 SQL Server 行轉(zhuǎn)列（Pivot） 的全攻略，包含三種常用寫法、完整示例、動(dòng)態(tài)列數(shù)處理、性能與易踩坑點(diǎn)。你可以直接復(fù)制粘貼模板改表名/字段名即可。
一、常見場(chǎng)景示例
假設(shè)原始表 Sales 結(jié)構(gòu)如下：
CREATE TABLE Sales (
SalesDate date,
Region nvarchar(50),
Product nvarchar(50),
Qty int
);
-- 示例數(shù)據(jù)
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);目標(biāo)：將 Product 的不同值（A、B…）變成列，數(shù)值填 SUM(Qty)，行按 SalesDate、Region。
二、寫法 1：PIVOT（固定列名）
當(dāng)你 已知列集合（比如只有 A/B/C）時(shí)，PIVOT 是最直觀的：
SELECT SalesDate, Region, ISNULL([A], 0) AS A, ISNULL([B], 0) AS B
FROM (
SELECT SalesDate, Region, Product, Qty
FROM Sales
) AS src
PIVOT (
SUM(Qty) FOR Product IN ([A], [B])
) AS p
ORDER BY SalesDate, Region;要點(diǎn)
FOR Product IN ([A], [B])中必須寫死列名。- 聚合函數(shù)可用
SUM/COUNT/MAX...。 - 若存在
NULL，可用ISNULL補(bǔ) 0。 - 多指標(biāo)（比如
SUM(Qty)與COUNT(*)同時(shí)）可用兩次 PIVOT 或用條件聚合（見寫法 2）。
三、寫法 2：條件聚合（CASE WHEN）
當(dāng)你想 靈活控制計(jì)算邏輯 或 一次輸出多個(gè)指標(biāo)，推薦條件聚合：
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;優(yōu)點(diǎn)
- 不需要
PIVOT語(yǔ)法，語(yǔ)義清晰、可讀性強(qiáng)。 - 可以在同一查詢里輸出多種計(jì)算指標(biāo)（數(shù)量、金額、最大值…）。
- 與窗口函數(shù)/更多條件結(jié)合更自然。
缺點(diǎn)
- 列集合仍需“寫死”。需要?jiǎng)討B(tài)列時(shí)見寫法 3。
四、寫法 3：動(dòng)態(tài)列名（Dynamic PIVOT）
當(dāng) 列值不固定（例如產(chǎn)品會(huì)新增），需要 動(dòng)態(tài)構(gòu)造 列清單。SQL Server 一般用 STRING_AGG（SQL 2017+）或 FOR XML PATH 生成列清單，再拼接動(dòng)態(tài) SQL。
4.1 適用于 SQL Server 2017+（STRING_AGG）
DECLARE @cols nvarchar(max);
DECLARE @sql nvarchar(max);
-- 1) 動(dòng)態(tài)列清單（加方括號(hào)并去重、排序）
SELECT @cols = STRING_AGG(QUOTENAME(Product), ',')
FROM (SELECT DISTINCT Product FROM Sales) d;
-- 2) 組裝動(dòng)態(tài) 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) 執(zhí)行
EXEC sp_executesql @sql;
``4.2 適用于 SQL Server 2016 及更早（FOR XML PATH）
DECLARE @cols nvarchar(max) = N'';
DECLARE @sql nvarchar(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;
``注意
QUOTENAME用來(lái)安全地給列名加[]，避免特殊字符出錯(cuò)。- 動(dòng)態(tài) SQL 結(jié)果集列名在編譯期未知，若要在上層程序接收，通常需要固定列或使用臨時(shí)表/表變量承接。
- 若列很多（上百上千），請(qǐng)同時(shí)考慮客戶端呈現(xiàn)是否可讀。
五、反向操作：列轉(zhuǎn)行（UNPIVOT或UNION ALL）
如果你有寬表（多列）要轉(zhuǎn)成長(zhǎng)表：
5.1 使用UNPIVOT
SELECT SalesDate, Region, Product, Qty
FROM (
SELECT SalesDate, Region, [A], [B]
FROM PivotedSales
) p
UNPIVOT (
Qty FOR Product IN ([A], [B])
) AS u;
``5.2 使用UNION ALL（更直觀、可控）
SELECT SalesDate, Region, 'A' AS Product, A AS Qty FROM PivotedSales UNION ALL SELECT SalesDate, Region, 'B', B FROM PivotedSales;
六、常見進(jìn)階需求
6.1 小計(jì)/合計(jì)
-- 在行轉(zhuǎn)列之前做匯總，再 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 ([A],[B])) p
UNION ALL
-- 合計(jì)行
SELECT SalesDate, 'Total' AS Region, [A], [B]
FROM (
SELECT SalesDate, Product, SUM(Qty) Qty
FROM Sales
GROUP BY SalesDate, Product
) s
PIVOT (SUM(Qty) FOR Product IN ([A],[B])) p
ORDER BY SalesDate, CASE WHEN Region='Total' THEN 1 ELSE 0 END, Region;
``6.2 按月/季度/年展開為列
SELECT Region,
SUM(CASE WHEN FORMAT(SalesDate,'yyyy-MM') = '2025-01' THEN Qty ELSE 0 END) AS [2025-01],
SUM(CASE WHEN FORMAT(SalesDate,'yyyy-MM') = '2025-02' THEN Qty ELSE 0 END) AS [2025-02]
FROM Sales
GROUP BY Region;更高性能可用
DATEFROMPARTS/YEAR/MONTH+ 字符拼接代替FORMAT（FORMAT對(duì)大表較慢）。
6.3 多指標(biāo)同時(shí)透視
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;
``七、性能與索引建議
- 先聚合再透視：對(duì)大表務(wù)必先
GROUP BY匯總，再PIVOT，能顯著減少數(shù)據(jù)量。 - 適配索引：
- 行轉(zhuǎn)列通常按（行維度列 + 列維度列）聚合，如示例按
SalesDate, Region, Product。 - 可以考慮覆蓋索引：
CREATE INDEX IX_Sales_Pivot ON Sales (SalesDate, Region, Product) INCLUDE (Qty);
- 行轉(zhuǎn)列通常按（行維度列 + 列維度列）聚合，如示例按
- 避免函數(shù)包裝索引列：例如在謂詞里用
FORMAT(SalesDate, ...)會(huì)導(dǎo)致索引失效，改用SalesDate >= @d1 AND SalesDate < @d2。 - 控制列數(shù)量：輸出列過(guò)多會(huì)影響網(wǎng)絡(luò)傳輸與結(jié)果集處理；必要時(shí)分頁(yè)或拆查詢。
- NULL 處理：
PIVOT得到NULL很常見，展示前用ISNULL/COALESCE。 - 權(quán)限與安全：動(dòng)態(tài) SQL 用
QUOTENAME防止注入；盡量不要直接拼接來(lái)自用戶輸入的列名/表名。
八、可直接替換的最簡(jiǎn)模板
固定列（PIVOT）
SELECT 維度列1, 維度列2, ISNULL([列值1],0) AS 列值1, ISNULL([列值2],0) AS 列值2
FROM (
SELECT 維度列1, 維度列2, 列名來(lái)源列, 度量列
FROM 源表
) s
PIVOT (
聚合函數(shù)(度量列) FOR 列名來(lái)源列 IN ([列值1],[列值2])
) p;條件聚合
SELECT 維度列1, 維度列2,
SUM(CASE WHEN 列名來(lái)源列='列值1' THEN 度量列 ELSE 0 END) AS 列值1,
SUM(CASE WHEN 列名來(lái)源列='列值2' THEN 度量列 ELSE 0 END) AS 列值2
FROM 源表
GROUP BY 維度列1, 維度列2;動(dòng)態(tài)列（2017+）
DECLARE @cols nvarchar(max), @sql nvarchar(max);
SELECT @cols = STRING_AGG(QUOTENAME(列名來(lái)源列), ',')
FROM (SELECT DISTINCT 列名來(lái)源列 FROM 源表) d;
SET @sql = N'
SELECT 維度列1, 維度列2, ' + @cols + N'
FROM (SELECT 維度列1, 維度列2, 列名來(lái)源列, 度量列 FROM 源表) s
PIVOT (聚合函數(shù)(度量列) FOR 列名來(lái)源列 IN (' + @cols + N')) p;';
EXEC sp_executesql @sql;到此這篇關(guān)于SQL Server 中的表進(jìn)行行轉(zhuǎn)列場(chǎng)景示例的文章就介紹到這了,更多相關(guān)sqlserver行轉(zhuǎn)列內(nèi)容請(qǐng)搜索腳本之家以前的文章或繼續(xù)瀏覽下面的相關(guān)文章希望大家以后多多支持腳本之家！
相關(guān)文章
這篇文章主要介紹了Oracle 刪除用戶和表空間詳細(xì)介紹的相關(guān)資料,需要的朋友可以參考下2016-12-12
解讀SQL一些語(yǔ)句執(zhí)行后出現(xiàn)異常不會(huì)回滾的問(wèn)題
這篇文章主要介紹了解讀SQL一些語(yǔ)句執(zhí)行后出現(xiàn)異常不會(huì)回滾的問(wèn)題，具有很好的參考價(jià)值，希望對(duì)大家有所幫助。如有錯(cuò)誤或未考慮完全的地方，望不吝賜教2023-04-04
ms sql server中實(shí)現(xiàn)的unix時(shí)間戳函數(shù)（含生成和格式化，可以和mysql兼容）
這篇文章主要介紹了ms sql server中實(shí)現(xiàn)的unix時(shí)間戳函數(shù),含生成和格式化UNIX_TIMESTAMP、from_unixtime兩個(gè)函數(shù),可以和mysql兼容,需要的朋友可以參考下2014-07-07
聚集索引，數(shù)據(jù)實(shí)際上是按順序存儲(chǔ)的，數(shù)據(jù)頁(yè)就在索引頁(yè)上。就好像參考手冊(cè)將所有主題按順序編排一樣。一旦找到了所要搜索的數(shù)據(jù)，就完成了這次搜索，對(duì)于非聚集索引，索引是安全獨(dú)立于數(shù)據(jù)本身結(jié)構(gòu)的，在索引中找到了尋找的數(shù)據(jù)，然后通過(guò)指針定位到實(shí)際的數(shù)據(jù)2012-07-07
SQLServer 數(shù)據(jù)庫(kù)故障修復(fù)頂級(jí)技巧之一
SQL Server 2005 和 2008 有幾個(gè)關(guān)于高可用性的選項(xiàng)，如日志傳輸、副本和數(shù)據(jù)庫(kù)鏡像。2010-04-04
本文詳細(xì)講解了SQL Server中索引的用法，文中通過(guò)示例代碼介紹的非常詳細(xì)。對(duì)大家的學(xué)習(xí)或工作具有一定的參考借鑒價(jià)值，需要的朋友可以參考下2022-05-05
這篇文章主要為大家詳細(xì)介紹了sql server 2012安裝程序圖集合,具有一定的參考價(jià)值，感興趣的小伙伴們可以參考一下2016-09-09
sql server 2000阻塞和死鎖問(wèn)題的查看與解決方法
在實(shí)際引用當(dāng)中，數(shù)據(jù)庫(kù)阻塞和死鎖在程序開發(fā)過(guò)程經(jīng)常出現(xiàn)，下面通過(guò)介紹數(shù)據(jù)庫(kù)阻塞和數(shù)據(jù)庫(kù)死鎖，并提供查看和解決阻塞和死鎖的方法2014-01-01
SQL查詢服務(wù)器下所有數(shù)據(jù)庫(kù)及數(shù)據(jù)庫(kù)的全部表
這篇文章主要介紹了SQL查詢服務(wù)器下所有數(shù)據(jù)庫(kù)，數(shù)據(jù)庫(kù)的全部表,本文通過(guò)實(shí)例代碼給大家介紹的非常詳細(xì)，對(duì)大家的學(xué)習(xí)或工作具有一定的參考借鑒價(jià)值，需要的朋友可以參考下2023-05-05
數(shù)據(jù)文件的組合,稱作文件組（File Group）,數(shù)據(jù)庫(kù)不能直接設(shè)置存儲(chǔ)數(shù)據(jù)的數(shù)據(jù)文件,而是通過(guò)文件組來(lái)指定,本文給大家詳細(xì)的介紹了SQL Server文件組,并通過(guò)代碼講解的非常詳細(xì),需要的朋友可以參考下2024-03-03