您现在的位置：程式碼之家 >> 數據庫知識 > >> SQL Server 資料庫 >> 關於SqlServer

SQL Server 2012 行轉列 (PIVOT) 實戰：動態列、UNPIVOT 組合、參數化模板

編輯：關於SqlServer

SQL Server 2005+ 引入 PIVOT/UNPIVOT 關係運算符，原生支持行列轉換。本文以產品審核記錄表為例，演示靜態/動態 PIVOT、UNPIVOT+PIVOT 組合實現「行→列→行」雙向轉換，並提供參數化動態模板可直接生產復用。

示例表結構

CREATE TABLE dbo.ProductAuditRecord (
    ParID NCHAR(12) PRIMARY KEY,
    MOID NCHAR(12), LotSN NVARCHAR(50),
    CosmeticInspection NCHAR(12), FunctionalTest NCHAR(12),
    UNumber NCHAR(50), LeadWire NCHAR(50),
    ResourceID NCHAR(12), UserID NCHAR(12),
    Remark NVARCHAR(100), CreatDate DATETIME,
    ProductID NCHAR(12), NextID INT
);

場景一：靜態 PIVOT (已知列值)

將審核項目 (CosmeticInspection, FunctionalTest) 由行轉為列。

SELECT ProductID, 
       [CosmeticInspection] AS 外觀檢驗,
       [FunctionalTest] AS 功能測試
FROM (
    SELECT ProductID, InspectionType, Result
    FROM ProductAuditRecord
    UNPIVOT (Result FOR InspectionType IN (CosmeticInspection, FunctionalTest)) AS u
) AS src
PIVOT (MAX(Result) FOR InspectionType IN ([CosmeticInspection], [FunctionalTest])) AS p;

場景二：動態列值 (未知/可變審核項目)

DECLARE @Cols NVARCHAR(MAX), @SQL NVARCHAR(MAX);

-- 1. 獲取所有不同的審核項目作為列
SELECT @Cols = STRING_AGG(QUOTENAME(InspectionType), ',')  -- SQL 2017+
-- SQL 2012/2014/2016 兼容寫法：
-- SELECT @Cols = COALESCE(@Cols + ',', '') + QUOTENAME(InspectionType)
FROM (SELECT DISTINCT InspectionType FROM (
    SELECT CosmeticInspection AS InspectionType FROM ProductAuditRecord
    UNION SELECT FunctionalTest FROM ProductAuditRecord
    UNION SELECT UNumber FROM ProductAuditRecord
    UNION SELECT LeadWire FROM ProductAuditRecord
) t) d;

-- 2. 構建動態 PIVOT
SET @SQL = N'
SELECT ProductID, ' + @Cols + N'
FROM (
    SELECT ProductID, InspectionType, Result
    FROM ProductAuditRecord
    UNPIVOT (Result FOR InspectionType IN (' + REPLACE(@Cols, ']', '],[') + N')) AS u
) AS src
PIVOT (MAX(Result) FOR InspectionType IN (' + @Cols + N')) AS p;';

EXEC sp_executesql @SQL;

場景三：標準測試數據 - 月份/指標雙維轉換

-- 測試表
CREATE TABLE #Test (月份 VARCHAR(4), 工資 INT, 福利 INT, 獎金 INT);
INSERT #Test VALUES 
('1月',100,200,300),('2月',110,210,310),('3月',120,220,320),('4月',130,230,330);

-- 目標：行(指標) → 列(月份)
-- 步驟 1: UNPIVOT 將 3 個指標列轉為 行(指標, 月份, 金額)
-- 步驟 2: PIVOT 將 月份 行轉為 列
SELECT 考核月份, [1月], [2月], [3月], [4月]
FROM (
    SELECT 考核月份, 月份, 金額
    FROM (
        SELECT 月份, 工資, 福利, 獎金 FROM #Test
    ) src
    UNPIVOT (金額 FOR 考核月份 IN (工資, 福利, 獎金)) AS u
) t
PIVOT (MAX(金額) FOR 月份 IN ([1月],[2月],[3月],[4月])) AS p;

結果：

考核月份  1月  2月  3月  4月
福利      200  210  220  230
工資      100  110  120  130
獎金      300  310  320  330

參數化動態 PIVOT 通用模板 (生產級)

CREATE PROCEDURE usp_DynamicPivot
    @TableName SYSNAME,           -- 源表/視圖
    @GroupCol SYSNAME,            -- 分組列 (行標識)
    @PivotCol SYSNAME,            -- 行轉列列 (列名來源)
    @ValueCol SYSNAME,            -- 值列 (聚合來源)
    @AggFunc SYSNAME = 'MAX',     -- 聚合函數 MAX/SUM/AVG/MIN
    @WhereClause NVARCHAR(MAX) = '' -- 可選過濾
AS
BEGIN
    SET NOCOUNT ON;
    DECLARE @Cols NVARCHAR(MAX), @SQL NVARCHAR(MAX);
    
    -- 1. 動態獲取列清單 (受 WHERE 影響)
    SET @SQL = N'SELECT @ColsOut = STRING_AGG(QUOTENAME(' + QUOTENAME(@PivotCol, '''') + N'), '','') 
                 FROM ' + QUOTENAME(@TableName) + @WhereClause + N' GROUP BY ' + QUOTENAME(@PivotCol);
    -- 2012/2014/2016 替換 STRING_AGG 為 COALESCE 迴圈
    
    EXEC sp_executesql @SQL, N'@ColsOut NVARCHAR(MAX) OUTPUT', @ColsOut = @Cols OUTPUT;
    
    IF @Cols IS NULL OR @Cols = '' RETURN; -- 無數據
    
    -- 2. 構建完整 PIVOT
    SET @SQL = N'
    SELECT ' + QUOTENAME(@GroupCol) + N', ' + @Cols + N'
    FROM (
        SELECT ' + QUOTENAME(@GroupCol) + N', ' + QUOTENAME(@PivotCol) + N', ' + QUOTENAME(@ValueCol) + N'
        FROM ' + QUOTENAME(@TableName) + @WhereClause + N'
    ) src
    PIVOT (' + @AggFunc + N'(' + QUOTENAME(@ValueCol) + N') FOR ' + QUOTENAME(@PivotCol) + N' IN (' + @Cols + N')) pvt
    ORDER BY ' + QUOTENAME(@GroupCol) + N';';
    
    EXEC sp_executesql @SQL;
END;
GO

-- 調用示例
EXEC usp_DynamicPivot 
    @TableName = 'ProductAuditRecord',
    @GroupCol = 'ProductID',
    @PivotCol = 'InspectionType',  -- 需先 UNPIVOT 或視圖預處理
    @ValueCol = 'Result',
    @WhereClause = ' WHERE CreatDate >= ''2024-01-01''';