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
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