SQL Server 動態交叉表 (Cross-Tab) 預存程序實作

需求背景

資料庫原生不支援交叉表（樞紐分析），需自行撰寫動態 SQL、游標、暫存表組合實現。

核心邏輯

1. 取得列欄位唯一值，動態建立交叉表結構（CREATE TABLE）。
2. 將列欄位值寫入交叉表第一欄。
3. 彙總資料 (GROUP BY 行, 列) 存入暫存表。
4. 游標逐筆讀取彙總資料，動態 UPDATE 交叉表對應欄位。

完整預存程序碼

CREATE PROCEDURE UP_CrossTab
    @TableName  VARCHAR(30),  -- 來源表
    @RowField   VARCHAR(30),  -- 行欄位
    @ColField   VARCHAR(30),  -- 列欄位
    @DataField  VARCHAR(30)   -- 彙總欄位
AS
BEGIN
    SET NOCOUNT ON;
    DECLARE @SQL VARCHAR(8000), @Field VARCHAR(30);

    -- 1. 取得列欄位唯一值
    SELECT @SQL = 'SELECT DISTINCT ' + @ColField + ' FROM ' + @TableName;
    CREATE TABLE #ColValues (Val VARCHAR(30));
    INSERT INTO #ColValues EXEC(@SQL);

    -- 2. 動態建立交叉表
    SELECT @SQL = 'CREATE TABLE #CrossTab (' + @RowField + ' VARCHAR(30),';
    DECLARE curCol CURSOR LOCAL FOR SELECT Val FROM #ColValues;
    OPEN curCol;
    FETCH curCol INTO @Field;
    WHILE @@FETCH_STATUS = 0
    BEGIN
        SELECT @SQL = @SQL + '[' + @Field + '] DECIMAL(18,2) DEFAULT 0,';
        FETCH curCol INTO @Field;
    END
    CLOSE curCol; DEALLOCATE curCol;
    SELECT @SQL = LEFT(@SQL, LEN(@SQL)-1) + ')';
    EXEC(@SQL);

    -- 3. 寫入行欄位值
    SELECT @SQL = 'INSERT INTO #CrossTab(' + @RowField + ') SELECT DISTINCT ' + @RowField + ' FROM ' + @TableName;
    EXEC(@SQL);

    -- 4. 彙總資料
    SELECT @SQL = 'CREATE TABLE #Agg (' + @RowField + ' VARCHAR(30), ' + @ColField + ' VARCHAR(30), ' + @DataField + ' DECIMAL(18,2))';
    EXEC(@SQL);
    SELECT @SQL = 'INSERT INTO #Agg SELECT ' + @RowField + ', ' + @ColField + ', SUM(' + @DataField + ') FROM ' + @TableName + ' GROUP BY ' + @RowField + ', ' + @ColField;
    EXEC(@SQL);

    -- 5. 回寫交叉表
    DECLARE @RowVal VARCHAR(30), @ColVal VARCHAR(30), @AggVal DECIMAL(18,2), @ColName VARCHAR(50);
    DECLARE curAgg CURSOR LOCAL FOR SELECT * FROM #Agg;
    OPEN curAgg;
    FETCH curAgg INTO @RowVal, @ColVal, @AggVal;
    WHILE @@FETCH_STATUS = 0
    BEGIN
        SELECT @ColName = '[' + @ColVal + ']';
        SELECT @SQL = 'UPDATE #CrossTab SET ' + @ColName + ' = ' + CAST(@AggVal AS VARCHAR(30)) + ' WHERE ' + @RowField + ' = ''' + @RowVal + '''';
        EXEC(@SQL);
        FETCH curAgg INTO @RowVal, @ColVal, @AggVal;
    END
    CLOSE curAgg; DEALLOCATE curAgg;

    -- 6. 輸出結果
    SELECT * FROM #CrossTab;
END
GO

使用範例

EXEC UP_CrossTab 'Sales', 'Store', 'Year', 'Qty'

現代替代方案 (SQL Server 2005+)

SELECT * FROM
(
    SELECT Store, Year, Qty FROM Sales
) AS src
PIVOT (SUM(Qty) FOR Year IN ([2003],[2004],[2005])) AS pvt

PIVOT/UNPIVOT 運算子大幅簡化交叉表查詢，建議新專案優先使用。