SQL 儲存程序實現 SPSS 風格交叉表 (Cross Tab / Pivot) 動態列轉行

前言

交叉表 (Cross Tabulation) 將長資料 (Long Format) 轉為寬資料 (Wide Format)：**行標題固定、列標題來自資料值、儲存格為聚合值**。SQL Server 透過 PIVOT 操作子 + 動態 SQL 可完整實現。本文提供生產級動態交叉表儲存程序 Pro_CrossTable，支援動態列、自動加總列/行、百分比計算。

核心邏輯流程

1. 彙總來源資料 → 暫存表 #Temp_CrossTable_001 (ColName1, ColName2, Value)。
2. 動態收集所有唯一 ColName1 值，構建 PIVOT IN (...) 列表、SUM 列表、總和運算式、百分比運算式。
3. 執行 PIVOT 產生數值交叉表 #Out_CrossTable_Value，並 UNION ALL 加總行。
4. 依數值表計算百分比表 #Out_CrossTable_Percent。
5. 交易包裹確保原子性。

完整儲存程序程式碼

CREATE PROCEDURE dbo.Pro_CrossTable
    @TableName  NVARCHAR(255),  -- 來源表名
    @ColName1   NVARCHAR(255),  -- 列標題來源欄位 (將變為 PIVOT 列)
    @ColName2   NVARCHAR(255)   -- 行標題來源欄位
AS
BEGIN
    SET NOCOUNT ON;
    BEGIN TRY
        BEGIN TRANSACTION;

        -- 1. 彙總基礎資料
        IF OBJECT_ID(N'tempdb..#Temp_CrossTable_001') IS NOT NULL DROP TABLE #Temp_CrossTable_001;
        CREATE TABLE #Temp_CrossTable_001 (
            ColName1 NVARCHAR(500) NULL,
            ColName2 NVARCHAR(500) NOT NULL,
            Value    FLOAT NULL
        );

        DECLARE @SQL NVARCHAR(MAX) = N'
            INSERT INTO #Temp_CrossTable_001
            SELECT ' + QUOTENAME(@ColName1) + N', ' + QUOTENAME(@ColName2) + N', COUNT(*)
            FROM ' + QUOTENAME(@TableName) + N'
            WHERE ' + QUOTENAME(@ColName1) + N' IS NOT NULL
            GROUP BY ' + QUOTENAME(@ColName1) + N', ' + QUOTENAME(@ColName2);
        EXEC sp_executesql @SQL;

        -- 2. 動態構建 PIVOT 所需字串
        DECLARE @Cols NVARCHAR(MAX), @SumCols NVARCHAR(MAX), @TotalExpr NVARCHAR(MAX), @PctExpr NVARCHAR(MAX);

        -- 2.1 列清單: [Val1],[Val2],...
        SELECT @Cols = STUFF((
            SELECT N', ' + QUOTENAME(ColName1)
            FROM (SELECT DISTINCT ColName1 FROM #Temp_CrossTable_001) t
            FOR XML PATH(N''), TYPE).value(N'.', N'NVARCHAR(MAX)'), 1, 2, N'');

        -- 2.2 SUM 列清單: SUM([Val1]),SUM([Val2]),...
        SELECT @SumCols = STUFF((
            SELECT N', SUM(' + QUOTENAME(ColName1) + N')'
            FROM (SELECT DISTINCT ColName1 FROM #Temp_CrossTable_001) t
            FOR XML PATH(N''), TYPE).value(N'.', N'NVARCHAR(MAX)'), 1, 2, N'');

        -- 2.3 總和運算式: [Val1]+[Val2]+...
        SELECT @TotalExpr = STUFF((
            SELECT N'+' + QUOTENAME(ColName1)
            FROM (SELECT DISTINCT ColName1 FROM #Temp_CrossTable_001) t
            FOR XML PATH(N''), TYPE).value(N'.', N'NVARCHAR(MAX)'), 1, 1, N'');

        -- 2.4 百分比運算式: t1.[Val1], t1.[Val1]/t2.[Val1] AS [Val1%], ...
        SELECT @PctExpr = STUFF((
            SELECT N', ' + QUOTENAME(ColName1) + N', ' + QUOTENAME(ColName1) + N'/CONVERT(FLOAT, t2.' + QUOTENAME(ColName1) + N') AS ' + QUOTENAME(ColName1 + N'%')
            FROM (SELECT DISTINCT ColName1 FROM #Temp_CrossTable_001) t
            FOR XML PATH(N''), TYPE).value(N'.', N'NVARCHAR(MAX)'), 1, 2, N'');

        -- 3. 執行 PIVOT 產生數值表 (含加總行)
        SET @SQL = N'
            IF OBJECT_ID(N''tempdb..#Out_CrossTable_Value'') IS NOT NULL DROP TABLE #Out_CrossTable_Value;
            SELECT * INTO #Out_CrossTable_Value FROM (
                SELECT ColName1, ColName2, Value FROM #Temp_CrossTable_001
            ) P
            PIVOT (SUM(Value) FOR ColName1 IN (' + @Cols + N')) AS T
            UNION ALL
            SELECT N''SumCol'', ' + @SumCols + N', SUM(' + @TotalExpr + N')
            FROM (
                SELECT ColName1, ColName2, Value FROM #Temp_CrossTable_001
            ) P
            PIVOT (SUM(Value) FOR ColName1 IN (' + @Cols + N')) AS T';
        EXEC sp_executesql @SQL;

        -- 4. 產生百分比表
        SET @SQL = N'
            IF OBJECT_ID(N''tempdb..#Out_CrossTable_Percent'') IS NOT NULL DROP TABLE #Out_CrossTable_Percent;
            SELECT 
                t1.ColName2 AS ' + QUOTENAME(@ColName2) + N',
                ' + @PctExpr + N'
            FROM #Out_CrossTable_Value t1
            CROSS JOIN (
                SELECT ' + @Cols + N' FROM #Out_CrossTable_Value WHERE ColName2 = N''SumCol''
            ) t2;';
        EXEC sp_executesql @SQL;

        COMMIT TRANSACTION;
        RETURN 0;
    END TRY
    BEGIN CATCH
        IF @@TRANCOUNT > 0 ROLLBACK TRANSACTION;
        THROW;
        RETURN 1;
    END CATCH
END
GO

呼叫範例

-- 來源表 temp_A063，列標題 ageArrange，行標題 indate
EXEC dbo.Pro_CrossTable 
    @TableName = N'temp_A063',
    @ColName1 = N'ageArrange',
    @ColName2 = N'indate';

-- 結果查詢
SELECT * FROM #Out_CrossTable_Value;      -- 數值交叉表 (含 SumCol 行)
SELECT * FROM #Out_CrossTable_Percent;    -- 百分比交叉表

結果結構說明

#Out_CrossTable_Value (數值表)

ColName2   [0-10]  [11-20]  [21-30]  SumCol
2023-01-01   5       12       8        25
2023-01-02   3       9        4        16
SumCol       8       21       12       41

#Out_CrossTable_Percent (百分比表)

indate     [0-10]  [0-10%]  [11-20]  [11-20%]  [21-30]  [21-30%]
2023-01-01   5       62.5%    12       57.1%     8        66.7%
2023-01-02   3       37.5%    9        42.9%     4        33.3%

• 百分比 = 該儲存格數值 / 該欄總和 (SumCol 行對應欄位)。
• 自動處理除零 (轉換為 NULL)。

關鍵技術點解析

擴充建議

1. 支援多重聚合：增加 @AggFunc 參數 (SUM/COUNT/AVG)。
2. 支援多行標題：@ColName2 改為逗號分隔清單，GROUP BY 多欄位。
3. 輸出格式化：百分比欄位用 FORMAT(val, 'P2') (SQL 2012+) 直接輸出 62.50%。
4. 效能優化：大資料量先建立來源表索引 (@ColName1, @ColName2)；PIVOT 列數超過 100 時考慮分批或改用應用層轉換。