行轉列 (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');
SELECT ProductID,
[CosmeticInspection], [FunctionalTest]
FROM (
SELECT ProductID, AuditItem, AuditResult
FROM dbo.ProductAuditRecord
) src
PIVOT (
COUNT(AuditResult) FOR AuditItem IN ([CosmeticInspection], [FunctionalTest])
) pvt;
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, '');
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;
| 優化方向 | 做法 | 效果 |
|---|---|---|
| 縮小掃描範圍 | PIVOT 前先用 WHERE/索引過濾日期/產品 | 減少輸入行數,降低記憶體/TempDB 壓力 |
| 預聚合 | 子查詢先 `GROUP BY ProductID, AuditItem, AuditResult` 再 PIVOT | 輸入行數從明細級降至聚合級,加速數量級 |
| 索引支援 | 建立 `(ProductID, AuditItem, AuditResult) INCLUDE (AuditDate)` 覆蓋索引 | 避免 Key Lookup,實現 Index Only Scan |
| 避免動態 SQL 重編譯 | 參數化 `sp_executesql` + `OPTION (RECOMPILE)` 僅在列變化時重編譯 | 平衡計畫重用與參數嗅探 |