萬盛學電腦網

您现在的位置: 萬盛學電腦網 >> 程式設計 >  >> 資料庫 >> MS SQL 資料庫

SQL Server 動態交叉表 (PIVOT) 存儲過程:行轉列通用實現

需求場景

給定任意表、行標題欄、列標題欄、數值欄,動態產生交叉表(樞紐分析),如 SPSS/Excel 樞紐表功能。

存儲過程核心邏輯

CREATE PROCEDURE [dbo].[Pro_CrossTable]
    @tableName  NVARCHAR(255),  -- 來源表
    @colName1   NVARCHAR(255),  -- 行標題 (轉為列)
    @colName2   NVARCHAR(255),  -- 列標題 (保持行)
    @valName    NVARCHAR(255) = N'cnt'  -- 數值欄,預設 COUNT(*)
AS
BEGIN
    SET NOCOUNT ON;

    -- 1. 建立暫存中間表:行標題、列標題、數值
    IF OBJECT_ID('tempdb..#CrossTmp') IS NOT NULL DROP TABLE #CrossTmp;
    CREATE TABLE #CrossTmp (
        RowLabel NVARCHAR(500) NULL,
        ColLabel NVARCHAR(500) NOT NULL,
        Val FLOAT NULL
    );

    -- 動態彙總 SQL
    DECLARE @sql NVARCHAR(MAX) = N'
        INSERT INTO #CrossTmp (RowLabel, ColLabel, Val)
        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) + N';';
    EXEC sp_executesql @sql;

    -- 2. 動態產生欄位清單:[值1], [值2], ...
    DECLARE @cols NVARCHAR(MAX), @sumCols NVARCHAR(MAX);
    SELECT @cols = STUFF((
        SELECT N'], [' + ColLabel FROM (
            SELECT DISTINCT ColLabel FROM #CrossTmp
        ) t FOR XML PATH(''), TYPE).value('.', 'NVARCHAR(MAX)'), 1, 2, '') + N']';

    SELECT @sumCols = STUFF((
        SELECT N'], SUM([' + ColLabel + N'])' FROM (
            SELECT DISTINCT ColLabel FROM #CrossTmp
        ) t FOR XML PATH(''), TYPE).value('.', 'NVARCHAR(MAX)'), 1, 2, '') + N']';

    -- 3. 動態 PIVOT 查詢
    SET @sql = N'
        SELECT RowLabel, ' + @sumCols + N'
        FROM #CrossTmp
        PIVOT (SUM(Val) FOR ColLabel IN (' + @cols + N')) pvt
        ORDER BY RowLabel;';

    EXEC sp_executesql @sql;
END
GO

呼叫範例

EXEC dbo.Pro_CrossTable 
    @tableName = N'temp_A063',
    @colName1 = N'ageArrange',  -- 行:年齡層
    @colName2 = N'indate';      -- 列:進院日期

關鍵技術點

  • QUOTENAME 防止識別碼注入,處理含空格/保留字的欄位名。
  • FOR XML PATH('') + STUFF 高效串聯欄位清單,無需游標迴圈。
  • PIVOT 運算子將 ColLabel 不同值展開為獨立欄位,聚合函數為 SUM(Val)。
  • 中間表 #CrossTmp 隔離動態 SQL,避免重複掃描大表。

擴充方向

  • 支援多數值欄(SUM、AVG、MAX、MIN 切換)。
  • 加入總計行/列(GROUPING SETS 或 ROLLUP)。
  • 輸出為 JSON/XML 供前端直接渲染。
  • 參數化 @valName 允許指定數值欄而非固定 COUNT。

效能考量

  1. 來源表需在 (@colName1, @colName2) 上有索引支援 GROUP BY。
  2. 中間表結果集應遠小於來源表(高基數列標題會導致列爆炸,建議限制 Top N 或分桶)。
  3. 動態 SQL 編譯開銷,高頻呼叫可考慮快取編譯計畫或改用應用層 PIVOT。
copyright © 萬盛學電腦網 all rights reserved