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