Someone asked me this question and it took me a while to figured out the solution. It look very simple with the new features available in SQL Server 2008.
Here is the example
if object_id('tempdb..#temp') is not null
drop table #temp
create table #temp
(
[Name] varchar(10),
[m1] int,
[m2] int,
[m3] int,
[m4] int
)
insert into #temp([Name],[m1],[m2],[m3],[m4])values('N1', 34, 36, 37, 58)
insert into #temp([Name],[m1],[m2],[m3],[m4])values('N2', 35, 31, 145, 67)
insert into #temp([Name],[m1],[m2],[m3],[m4])values('N3', 34, 65, 37, 34)
insert into #temp([Name],[m1],[m2],[m3],[m4])values('N4', 34, 78, 90, 58)
insert into #temp([Name],[m1],[m2],[m3],[m4])values('N5', 34, 47, 0, 18)
insert into #temp([Name],[m1],[m2],[m3],[m4])values('N6', 89, 36, 56, 60)
SELECT *, (
SELECT MAX(val) FROM (
VALUES (m1), (m2), (m3), (m4)
) AS value(val)) AS MaxVal
FROM #temp
Results
