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

SQL Server 動態交叉表 (Dynamic Pivot) 實作：通用預存程序與游標應用詳解

編輯：關於SqlServer

靜態交叉表 (PIVOT) 需預先知道欄位值，無法應對「欄位由資料動態決定」的報表需求 (如：銷售月份、產品類別隨資料增減)。本文提供一支 通用動態交叉表預存程序，利用游標動態建構 CASE WHEN 條件聚合語句，支援任意資料表、任意群組/統計欄位，並詳細解析游標四大操作步驟與語法。

一、通用動態交叉表預存程序

CREATE PROCEDURE proc_across_table
    @TableName    VARCHAR(50),  -- 來源資料表
    @NewColumn    VARCHAR(50),  -- 動態欄位來源 (如：訂單月份、產品類別)
    @GroupColumn  VARCHAR(50),  -- 群組依據 (列標題，如：商品名稱)
    @StatColumn   VARCHAR(50),  -- 統計數值欄位 (如：訂貨數量)
    @Operator     VARCHAR(10)   -- 統計運算 (SUM, COUNT, AVG, MAX, MIN)
AS
BEGIN
    SET NOCOUNT ON;

    DECLARE @SQL VARCHAR(1000), @Column VARCHAR(50);

    -- 1. 宣告游標：取得動態欄位的唯一值
    EXECUTE ('DECLARE cursor_new_column CURSOR FOR SELECT DISTINCT ' + @NewColumn + ' FROM ' + @TableName + ' FOR READ ONLY');

    -- 2. 開啟游標
    OPEN cursor_new_column;

    -- 3. 建構 SQL 頭部：群組欄位 + 總計欄位
    SET @SQL = 'SELECT ' + @GroupColumn + ', ' + @Operator + '(' + @StatColumn + ') AS [' + @Operator + ' of ' + @StatColumn + ']';

    -- 4. 遍歷游標，動態追加 CASE WHEN 條件聚合欄位
    WHILE (1=1)
    BEGIN
        FETCH NEXT FROM cursor_new_column INTO @Column;
        IF (@@FETCH_STATUS <> 0) BREAK;

        SET @SQL = @SQL + ', ' + @Operator + '(CASE ' + @NewColumn + ' WHEN ''' + @Column + ''' THEN ' + @StatColumn + ' ELSE NULL END) AS [' + @Column + ']';
    END

    -- 5. 完成 SQL 尾部：FROM + GROUP BY
    SET @SQL = @SQL + ' FROM ' + @TableName + ' GROUP BY ' + @GroupColumn;

    -- 6. 執行動態 SQL
    EXECUTE(@SQL);
    PRINT @SQL; -- 除錯用：輸出實際執行的 SQL

    -- 7. 清理游標資源
    CLOSE cursor_new_column;
    DEALLOCATE cursor_new_column;

    RETURN 0;
END
GO

二、呼叫範例

-- 以「商品銷售表」為例，依「商品名」分組，「訂單號」做動態欄位，統計「訂貨數量」總和
EXEC proc_across_table '商品銷售表', '訂單號', '商品名', '訂貨數量', 'SUM';

呼叫語法說明： 預存程序名稱後接參數，參數用單引號包裹，參數間以逗號分隔。

三、游標 (Cursor) 四大操作步驟詳解

游標是逐列處理結果集的機制，必須遵循固定順序：

宣告 (DECLARE CURSOR)：定義游標名稱、查詢來源、捲動選項、並行控制 (READ ONLY / UPDATE)。
開啟 (OPEN)：執行查詢、建立結果集、指標指向第一列前。
擷取 (FETCH)：將目前列資料讀入變數，指標移至下一列。常搭配 @@FETCH_STATUS 判斷結束 (0=成功, -1=失敗, -2=列不存在)。
關閉與釋放 (CLOSE / DEALLOCATE)：CLOSE 釋放結果集鎖定；DEALLOCATE 釋放游標結構記憶體，**兩者皆不可省略**。

DECLARE CURSOR 完整語法

DECLARE cursor_name [ INSENSITIVE ] [ SCROLL ] CURSOR
FOR select_statement
[ FOR { READ_ONLY | UPDATE [ OF column_name [ ,...n ] ] } ]

INSENSITIVE：建立暫存資料複本，隔離基底資料變更 (唯讀)。
SCROLL：支援前向/後向/絕對/相對擷取 (預設僅 FORWARD_ONLY)。
READ_ONLY：明確宣告唯讀，最佳化效能。

四、進階改良方向 (現代寫法)

SQL Server 2005+ 可改用 FOR XML PATH 或 STRING_AGG (2017+) 取代游標動態建構字串，效能更佳、程式碼更簡潔：

-- 現代動態 PIVOT 範例 (SQL 2017+)
DECLARE @cols NVARCHAR(MAX), @sql NVARCHAR(MAX);
SELECT @cols = STRING_AGG(QUOTENAME(訂單月份), ',') FROM (SELECT DISTINCT 訂單月份 FROM 銷售表) t;
SET @sql = N'SELECT 商品名, ' + @cols + N' FROM (SELECT 商品名, 訂單月份, 訂貨數量 FROM 銷售表) src PIVOT (SUM(訂貨數量) FOR 訂單月份 IN (' + @cols + N')) pvt';
EXEC sp_executesql @sql;

但本文提供的游標版本相容性最廣 (SQL 7.0+)，仍具參考價值。