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

SQL Server 動態交叉表/樞紐預存程序：游標驅動動態欄位生成實戰

編輯：關於SqlServer

交叉表 (Cross-tab/Pivot) 需將行資料轉為欄位，且欄位值不固定 (如屬性名稱動態增減)。本文解析一個以游標讀取屬性清單 → 動態拼接 CASE 聚合 → sp_executesql 執行的通用樞紐預存程序，適用於 EAV (Entity-Attribute-Value) 模型、動態報表產生器。

場景與基礎檢視表


-- 產品屬性 EAV 檢視表
CREATE VIEW V_product_params AS
SELECT 
    p.Type_GUID, p.Series_GUID, p.Product_No, p.Product_Name,
    pa.Param_No, pa.Param_Value
FROM Products p
JOIN Product_Attributes pa ON p.Product_GUID = pa.Product_GUID;

目標：輸入 @Series_GUID，輸出每產品一列，屬性值動態展開為欄位 F1, F2, F3...。

預存程序完整碼


CREATE PROCEDURE P_GetSeriesProductDetail
    @series_guid VARCHAR(40),
    @condition   VARCHAR(1000) = ''  -- 額外 WHERE 條件，如 'product_name LIKE ''%BK-1%'''
AS
BEGIN
    SET NOCOUNT ON;
    
    DECLARE @ParamNo NVARCHAR(5);
    DECLARE @SQL     NVARCHAR(MAX) = N'';
    
    -- 1. 宣告游標讀取該系列所有屬性代號
    DECLARE P_cursor CURSOR LOCAL FAST_FORWARD FOR
        SELECT Param_No FROM V_product_params WHERE Series_GUID = @series_guid;
    
    OPEN P_cursor;
    FETCH NEXT FROM P_cursor INTO @ParamNo;
    
    -- 2. 迴圈拼接動態 CASE 聚合欄位
    WHILE @@FETCH_STATUS = 0
    BEGIN
        SET @SQL = @SQL + N',
            MAX(CASE Param_No WHEN ' + QUOTENAME(@ParamNo, '''') + N' THEN Param_Value ELSE '''' END) AS F' + @ParamNo + NCHAR(13);
        FETCH NEXT FROM P_cursor INTO @ParamNo;
    END
    
    CLOSE P_cursor;
    DEALLOCATE P_cursor;
    
    -- 3. 組裝完整查詢
    SET @SQL = N'
        SELECT Type_GUID, Series_GUID, Product_No, Product_Name' + @SQL + N'
        FROM V_product_params 
        WHERE Series_GUID = ' + QUOTENAME(@series_guid, '''') + 
        CASE WHEN LTRIM(@condition) <> N'' THEN N' AND ' + @condition ELSE N'' END + N'
        GROUP BY Type_GUID, Series_GUID, Product_No, Product_Name';
    
    -- 4. 執行 (列印供除錯)
    PRINT @SQL;
    EXEC sp_executesql @SQL;
END;
GO

關鍵技術點解析

技術	作用	優勢
`CURSOR LOCAL FAST_FORWARD`	唯讀、順向、低開銷游標	避免臨時表、WHILE 迴圈效能佳
`QUOTENAME(@ParamNo, '''')`	安全轉義單引號，防 SQL 注入	處理屬性代號含引號/特殊字元
`MAX(CASE ...)` 條件聚合	將多行屬性轉為單行多欄	標準 SQL、無需 PIVOT 語法、支援任意型別
`sp_executesql`	參數化動態 SQL 執行	計畫重用、安全、支援輸出參數

呼叫範例


-- 查詢 Cylindrical1 系列所有產品
EXEC P_GetSeriesProductDetail 'Cylindrical1', '';

-- 查詢含 BK-1 的產品
EXEC P_GetSeriesProductDetail 'Cylindrical1', 'Product_Name LIKE ''%BK-1%''';

效能優化建議

索引： CREATE INDEX IX_Vpp_Series_Param ON V_product_params (Series_GUID, Param_No) INCLUDE (Param_Value, Product_No, Product_Name, Type_GUID); 實現覆蓋索引，避免書籤查閱。
參數化 @condition： 目前為字串拼接，若頻繁執行建議改為 sp_executesql @SQL, N'@cond VARCHAR(1000)', @cond 並將 @condition 參數化。
結果快取： 若屬性結構變動極低，可將生成的 @SQL 快取於資料表，依 Series_GUID + Condition_Hash 查找，避免重複游標拼接。
大量屬性： 若屬性 > 50，動態欄位過多導致網路傳輸大、應用解析難。建議改為應用層樞紐或分頁載入屬性詳情。

擴充：多值屬性支援

若單一產品同一屬性有多值 (如多顏色)，MAX 會取最大值。改用 STRING_AGG (SQL 2017+) 或 FOR XML PATH 串接：


STUFF((
    SELECT ',' + Param_Value 
    FROM V_product_params v2 
    WHERE v2.Product_No = v1.Product_No AND v2.Param_No = v1.Param_No
    FOR XML PATH('')), 1, 1, '') AS F_ParamNo

動態交叉表預存程序是「資料庫端報表引擎」的核心組件，將樞紐邏輯下推至資料層，大幅降低應用層資料傳輸量與轉置運算，是舊系統現代化、報表自助化的關鍵技術。