標準彙總函數 (MIN/MAX/SUM/AVG) 僅支援「同一欄位多列」運算。若需對「同一列多欄位」做彙總(如一筆資料的 6 個數值欄位求最大值),常見做法是 CASE WHEN 或 UNPIVOT,但欄位多時寫法冗長。本文介紹利用 FOR XML PATH 將列轉為 XML,再用 XQuery 還原為資料列集合,最後套用標準彙總函數的巧技。
DECLARE @t TABLE (
id SMALLINT,
a SMALLINT, b SMALLINT, c SMALLINT,
d SMALLINT, e SMALLINT, f SMALLINT
);
INSERT INTO @t SELECT 1, 1, 2, 3, 4, 6, 7 UNION ALL
SELECT 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;
| id | a | b | c | d | e | f | minValue | maxValue | sumValue | avgValue |
|---|---|---|---|---|---|---|---|---|---|---|
| 1 | 1 | 2 | 3 | 4 | 6 | 7 | 1 | 7 | 23 | 3 |
| 2 | 34 | 45 | 56 | 54 | 9 | 6 | 6 | 56 | 204 | 34 |
FOR XML PATH(''), TYPE:將符合 id 的單列轉為無根元素的 XML 片段,型別為 xml。doc.nodes('/a,b,c,d,e,f'):XQuery 針對每個子元素 (<a>, <b>...) 產生一列。query('text()') 取出文字內容 → 轉 VARCHAR → 轉 INT 得到純量值 r。MIN/MAX/SUM/AVG 完成跨欄彙總。UNPIVOT 或應用層處理。