Binaryworld - Maximum Value from 5 columns. ... [ SQL Server -> T-SQL ]

This script shows how to obtain the maximum value of 5 columns. It is possible to do this using CASE statement but the expression gets quite lengthy. This shows an easy way to use the MAX function itself & this approach can be extended to as many columns as you want.

Click here to copy the following block

create table #t (
id int identity ,
Col1 int , Col2 int , Col3 int , Col4 int , Col5 int
)
insert #t values( 5, 123, 9 , 11, 20 )
insert #t values( 35, 7, 16 ,38 , 45 )
insert #t values( 75, 87, 51 , 98 , 24 )
go
select id , max( val ) as MaxVal
from
(
select id , col ,
case col
when 1 then Col1
when 2 then Col2
when 3 then Col3
when 4 then Col4
when 5 then Col5
end as val
from #t
cross join
( select 1 as col
union all
select 2 union all select 3 union all
select 4 union all select 5
) As s
) as a
group by id