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 

This page was last updated on May 01, 2006 04:28 PM.