SQL2005中按列聚合（取N列中的最大最小等）

create table [ tb ] (ID int identity ( 1 , 1 ), a int , b int , c int , d int )

insert [ tb ] select 1 , 2 , 3 , 4     -- max: 4
union all select 5 , 2 , 8 , 3     -- max: 8
union all select 9 , 7 , 6 , 5     -- max: 9
union all select 0 , 9 , 2 , 2     -- max: 9

-- Method 1, by dobear
select ID,  Max_abcd = max ( [ value ] )
from tb
unpivot( [ value ] for [ abcd ] in ( [ a ] , [ b ] , [ c ] , [ d ] )) as U
group by ID

/*
ID          Max_abcd
----------- -----------
1           4
2           8
3           9
4           9

(4 行受影响)
*/

-- Method 2, by fcuandy
select ID = cast (b.ID as varchar ( 8 )), Max_abcd = cast (b.v as varchar ( 8 ))
from ( select x = cast (( select * from tb for xml path( ' r ' )) as xml)) a
cross apply
    ( select ID = x.query( ' ./ID/text() ' ),v = x.query( ' max(./*[local-name(.)!="ID"]) ' )
     from a.x.nodes( ' //r ' ) as t(x)) b
/*
ID       Max_abcd
-------- --------
1        4
2        8
3        9
4        9

(4 行受影响)
*/

drop table tb