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