create table test(이름 varchar(3),성적1 int,성적2 int,성적3 int,성적4 int,성적5 int)

insert test

select 'AAA',52,78,34,0,43 union all

select 'BBB',46,54,75,65,24 union all

select 'CCC',78,86,45,56,12 union all

select 'DDD',53,77,13,44,67 union all

select 'EEE',34,24,63,23,83

------------------------------------------

--MSSQL 2000

select identity(int,1,1) 순위,

       이름,

       max(case b.id when 1 then 성적1

                     when 2 then 성적2

                     when 3 then 성적3

                     when 4 then 성적4

                     when 5 then 성적5 end) 성적 into #tmp    

 from test a cross join

      (select 1 id union all

       select 2 union all

       select 3 union all

       select 4 union all

       select 5) b

 group by 이름

 order by 성적 desc

select * from #tmp

/*

순위          이름   성적

----------- ---- -----------

1           CCC  86

2           EEE  83

3           AAA  78

4           DDD  77

5           BBB  75

(5개 행 적용됨)

*/

--MSSQL 2005

select row_number() over(order by 성적 desc) 순위,*

  from (select 이름,

               max(case b.id when 1 then 성적1

                             when 2 then 성적2

                             when 3 then 성적3

                             when 4 then 성적4

                             when 5 then 성적5 end) 성적        

              from test a cross join

                  (select 1 id union all

                   select 2 union all

                   select 3 union all

                   select 4 union all

                   select 5) b

            group by 이름) t

 order by 성적 desc

/*

이름   성적

---- -----------

AAA  78

BBB  75

CCC  86

DDD  77

EEE  83

(5개 행 적용됨)

*/