Aggregate functions such as SQL max can only perform n-row operations on the same column. If n-column operations are performed, the case statement is generally used for determination. If the number of columns is small, it is easier to write, if there are too many columns, it will be troublesome.
--------------------------------------------------------------------------------
/*
Test name: use XML to calculate the aggregation between any columns
Function: Perform min, max, sum, and avg operations on the column data of a table.
Operating principle: Merge fields into xml before performing an xquery query and convert them to a row set for post-aggregation
*/
-- Create a test environment
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
-- Test statement
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 (d. n. query ('text () ') as varchar (max) as int) as r
From doc. nodes ('/a, B, c, d, e, f') D (n) tt
) C
/* Test results
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
*/