SQL Server 任意欄位間聚合運算：利用 XML + XQuery 實現跨列 MIN/MAX/SUM/AVG

標準彙總函數（`MAX`、`SUM` 等）僅能對「同一欄位的多列」運算。若需對「同一列的多個欄位」（如 a,b,c,d,e,f）做跨列聚合，傳統寫法需大量 `CASE` 判斷。本文介紹利用 `FOR XML PATH` 將欄位序列化為 XML，再以 XQuery 還原為資料列集，最後套用彙總函數的巧妙技巧。

核心邏輯

1. `FOR XML PATH(''), TYPE` 將單列轉為 XML 片段 `12...`。
2. `CROSS APPLY` + `.nodes('/a,b,c,d,e,f')` 將各節點拆解為資料列集。
3. 對拆解後的資料列集套用 `MIN/MAX/SUM/AVG`。

完整測試腳本

-- 建立測試資料
DECLARE @t TABLE (
    id SMALLINT,
    a SMALLINT, b SMALLINT, c SMALLINT,
    d SMALLINT, e SMALLINT, f SMALLINT
);
INSERT INTO @t VALUES
(1, 1, 2, 3, 4, 6, 7),
(2, 34, 45, 56, 54, 9, 6);

-- 跨列聚合查詢
SELECT a.*, c.*
FROM @t a
OUTER APPLY (
    SELECT doc = (
        SELECT * FROM @t AS doc WHERE id = a.id FOR XML PATH(''), TYPE
    )
) b
OUTER APPLY (
    SELECT
        MIN(r) AS minValue,
        MAX(r) AS maxValue,
        SUM(r) AS sumValue,
        AVG(r) AS avgValue
    FROM (
        SELECT CAST(CAST(d.n.query('text()') AS VARCHAR(MAX)) AS INT) AS r
        FROM doc.nodes('/a,b,c,d,e,f') D(n)
    ) tt
) c;

執行結果

優缺點分析

• 優點：欄位數多時程式碼極度精簡，無需手寫冗長 `CASE`/`UNPIVOT`。
• 缺點：XML 序列化/解析有效能開銷，不適合超大資料量（百萬列以上）高頻查詢；僅適用於數值型別相容的欄位組合。
• 替代方案：SQL Server 2008+ 可用 `UNPIVOT` 或 `CROSS APPLY VALUES` 獲得更好效能與可讀性。