萬盛學電腦網

您现在的位置: 萬盛學電腦網 >> 程式設計 >  >> 資料庫 >> MS SQL 資料庫

SQL Server 2012 行轉列 (PIVOT) 進階實戰:動態列、多值聚合與效能優化

行轉列 (Pivot) 是報表開發高頻需求。SQL Server 2005+ 引入 `PIVOT/UNPIVOT` 運算符,2012 進一步優化查詢優化器對動態列的支援。本文以真實產品稽核記錄表為例,示範靜態/動態 PIVOT、多欄位聚合、動態 SQL 參數化與效能調優全流程。

場景與示例資料

CREATE TABLE dbo.ProductAuditRecord (
    ParID NCHAR(12) PRIMARY KEY,
    ProductID NCHAR(12) NOT NULL,
    AuditItem NVARCHAR(20) NOT NULL,  -- 'CosmeticInspection', 'FunctionalTest', ...
    AuditResult NVARCHAR(20) NOT NULL, -- 'Pass', 'Fail', 'Rework'
    AuditDate DATETIME NOT NULL
);

-- 測試資料
INSERT INTO dbo.ProductAuditRecord VALUES
('P001','PRD001','CosmeticInspection','Pass','2024-01-01'),
('P002','PRD001','FunctionalTest','Fail','2024-01-01'),
('P003','PRD001','CosmeticInspection','Pass','2024-01-02'),
('P004','PRD001','FunctionalTest','Pass','2024-01-02');

1. 靜態 PIVOT (列固定)

SELECT ProductID,
       [CosmeticInspection], [FunctionalTest]
FROM (
    SELECT ProductID, AuditItem, AuditResult
    FROM dbo.ProductAuditRecord
) src
PIVOT (
    COUNT(AuditResult) FOR AuditItem IN ([CosmeticInspection], [FunctionalTest])
) pvt;

2. 動態 PIVOT (列隨資料變化)

DECLARE @cols NVARCHAR(MAX), @sql NVARCHAR(MAX);

SELECT @cols = STRING_AGG(QUOTENAME(AuditItem), ',')  -- 2017+ STRING_AGG
FROM (SELECT DISTINCT AuditItem FROM dbo.ProductAuditRecord) d;

SET @sql = N'
SELECT ProductID, ' + @cols + N'
FROM (
    SELECT ProductID, AuditItem, AuditResult
    FROM dbo.ProductAuditRecord
) src
PIVOT (
    COUNT(AuditResult) FOR AuditItem IN (' + @cols + N')
) pvt;';

EXEC sp_executesql @sql;

2012/2014 版本無 `STRING_AGG`,改用 `STUFF + FOR XML PATH` 拼接:

SELECT @cols = STUFF((
    SELECT ',' + QUOTENAME(AuditItem)
    FROM (SELECT DISTINCT AuditItem FROM dbo.ProductAuditRecord) d
    FOR XML PATH(''), TYPE).value('.', 'NVARCHAR(MAX)'), 1, 1, '');

3. 多值聚合:同時統計「通過數」與「失敗數」

SELECT ProductID,
       [CosmeticInspection_Pass], [CosmeticInspection_Fail],
       [FunctionalTest_Pass], [FunctionalTest_Fail]
FROM (
    SELECT ProductID, 
           AuditItem + '_' + AuditResult AS ItemResult,
           1 AS Cnt
    FROM dbo.ProductAuditRecord
) src
PIVOT (SUM(Cnt) FOR ItemResult IN (
    [CosmeticInspection_Pass], [CosmeticInspection_Fail],
    [FunctionalTest_Pass], [FunctionalTest_Fail]
)) pvt;

4. 效能優化關鍵點

5. UNPIVOT 逆向操作 (列轉行)

SELECT ProductID, AuditItem, AuditResult, Cnt
FROM WideTable
UNPIVOT (Cnt FOR ItemResult IN (
    [CosmeticInspection_Pass], [CosmeticInspection_Fail],
    [FunctionalTest_Pass], [FunctionalTest_Fail]
)) u
CROSS APPLY (VALUES 
    (LEFT(ItemResult, CHARINDEX('_', ItemResult)-1), 
     SUBSTRING(ItemResult, CHARINDEX('_', ItemResult)+1, 99))
) ca(AuditItem, AuditResult);

掌握動態 PIVOT + 預聚合 + 覆蓋索引 三板斧,即可應對 95% 以上行轉列報表場景,兼顧開發效率與執行效能。

copyright © 萬盛學電腦網 all rights reserved
優化方向做法效果
縮小掃描範圍PIVOT 前先用 WHERE/索引過濾日期/產品減少輸入行數,降低記憶體/TempDB 壓力
預聚合子查詢先 `GROUP BY ProductID, AuditItem, AuditResult` 再 PIVOT輸入行數從明細級降至聚合級,加速數量級
索引支援建立 `(ProductID, AuditItem, AuditResult) INCLUDE (AuditDate)` 覆蓋索引避免 Key Lookup,實現 Index Only Scan
避免動態 SQL 重編譯參數化 `sp_executesql` + `OPTION (RECOMPILE)` 僅在列變化時重編譯平衡計畫重用與參數嗅探