引言

在數(shù)據(jù)分析與報(bào)表生成場(chǎng)景中，行列轉(zhuǎn)換是一個(gè)高頻需求。SQL Server 提供了 PIVOT 和 UNPIVOT 兩個(gè)強(qiáng)大的運(yùn)算符，能夠幫助我們快速實(shí)現(xiàn)數(shù)據(jù)透視與逆透視操作。本文將結(jié)合具體示例，解析它們的核心用法。

一、PIVOT：將行轉(zhuǎn)換為列

PIVOT函數(shù)主要是用來(lái)將數(shù)據(jù)從行轉(zhuǎn)換成列。比如，如果有訂單數(shù)據(jù)表，里面有很多訂單的信息，可能按客戶ID、訂單日期等分組。使用PIVOT可以把這些重復(fù)的客戶信息排列成一個(gè)更緊湊的表格，每個(gè)客戶的訂單日期變成一列，這樣看起來(lái)更直觀。

核心作用

將某一列的唯一值作為新列名，并按需聚合關(guān)聯(lián)數(shù)據(jù)。

語(yǔ)法結(jié)構(gòu)

SELECT [非透視列], [透視列1], [透視列2], ...
FROM (
    SELECT [列1], [列2], [聚合列] 
    FROM 表
) AS 源表
PIVOT (
    聚合函數(shù)(聚合列)
    FOR [目標(biāo)列] IN ([透視值1], [透視值2], ...)
) AS 別名;

實(shí)戰(zhàn)示例

場(chǎng)景：統(tǒng)計(jì)各部門在不同季度的銷售額。

• 準(zhǔn)備數(shù)據(jù)

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);

• 執(zhí)行 PIVOT

SELECT Department, [Q1], [Q2], [Q3], [Q4]
FROM (
    SELECT Department, Quarter, Amount 
    FROM #Sales
) AS Src
PIVOT (
    SUM(Amount)
    FOR Quarter IN ([Q1], [Q2], [Q3], [Q4])
) AS Pvt;

輸出結(jié)果：

二、UNPIVOT：將列轉(zhuǎn)換為行

UNPIVOT函數(shù)，它的作用和PIVOT相反，是用來(lái)把數(shù)據(jù)從列轉(zhuǎn)換回行。比如，在PIVOT之后得到的一張表格里，如果需要進(jìn)一步細(xì)分?jǐn)?shù)據(jù)或者進(jìn)行其他操作，可以用UNPIVOT來(lái)恢復(fù)原來(lái)的多行結(jié)構(gòu)。

核心作用

將多列合并為兩列（屬性名+屬性值），實(shí)現(xiàn)數(shù)據(jù)逆向透視。

語(yǔ)法結(jié)構(gòu)

SELECT [非透視列], [屬性列], [值列]
FROM 表
UNPIVOT (
    值列 FOR 屬性列 IN ([列1], [列2], ...)
) AS 別名;

實(shí)戰(zhàn)示例

場(chǎng)景：將季度銷售額列還原為行結(jié)構(gòu)。

• 使用之前 PIVOT 的結(jié)果作為輸入

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);

• 執(zhí)行 UNPIVOT

SELECT Department, Quarter, Amount
FROM #PivotedSales
UNPIVOT (
    Amount FOR Quarter IN (Q1, Q2, Q3, Q4)
) AS Unpvt;

輸出結(jié)果：

三、關(guān)鍵注意事項(xiàng)

• 數(shù)據(jù)類型一致性UNPIVOT 的所有列必須具有兼容的數(shù)據(jù)類型。

• 處理 NULL 值PIVOT 會(huì)自動(dòng)過(guò)濾 NULL 值，可通過(guò) ISNULL() 或 COALESCE() 預(yù)處理。

• 動(dòng)態(tài)列處理當(dāng)透視列值不固定時(shí)，需使用動(dòng)態(tài) SQL 拼接列名（示例需另寫代碼實(shí)現(xiàn)）。

• 性能優(yōu)化對(duì)大型數(shù)據(jù)集建議建立合適索引，避免全表掃描。

四、典型應(yīng)用場(chǎng)景對(duì)比

五、總結(jié)

• PIVOT 通過(guò)聚合實(shí)現(xiàn)行轉(zhuǎn)列，適合制作匯總視圖
• UNPIVOT 通過(guò)逆向操作恢復(fù)數(shù)據(jù)結(jié)構(gòu)，適合數(shù)據(jù)清洗
• 二者配合使用可完成復(fù)雜數(shù)據(jù)轉(zhuǎn)換需求

到此這篇關(guān)于SQL Server中的PIVOT與UNPIVOT用法具體示例的文章就介紹到這了,更多相關(guān)SQLServer PIVOT與UNPIVOT用法內(nèi)容請(qǐng)搜索腳本之家以前的文章或繼續(xù)瀏覽下面的相關(guān)文章希望大家以后多多支持腳本之家！

最新評(píng)論