--sql 2000

select a , 
       max(case px when 1 then b else null end) col1,
       max(case px when 2 then b else null end) col2,
       max(case px when 3 then b else null end) col3
from
(
  select t.* , px = (select count(1) from tb where a = t.a and c > t.c) + 1 from tb t
) m
group by a
union all
select a , 
       max(case px when 1 then c else null end) col1,
       max(case px when 2 then c else null end) col2,
       max(case px when 3 then c else null end) col3
from
(
  select t.* , px = (select count(1) from tb where a = t.a and c > t.c) + 1 from tb t
) m
group by a
order by a , col1 

--sql 2005
select a , 
       max(case px when 1 then b else null end) col1,
       max(case px when 2 then b else null end) col2,
       max(case px when 3 then b else null end) col3
from
(
  select t.* , px = row_number() over(partition by a order by c desc) from tb t
) m
group by a
union all
select a , 
       max(case px when 1 then c else null end) col1,
       max(case px when 2 then c else null end) col2,
       max(case px when 3 then c else null end) col3
from
(
  select t.* , px = row_number() over(partition by a order by c desc) from tb t
) m
group by a
order by a , col1

------解决方案--------------------

SQL code
--sql 2000

select a , 
       max(case px when 1 then b else null end) col1,
       max(case px when 2 then b else null end) col2,
       max(case px when 3 then b else null end) col3,
       max(case px when 4 then b else null end) col4,
       max(case px when 5 then b else null end) col5,
       max(case px when 6 then b else null end) col6,
       max(case px when 7 then b else null end) col7,
       max(case px when 8 then b else null end) col8,
       max(case px when 9 then b else null end) col9,
       max(case px when 10 then b else null end) col10
from
(
  select t.* , px = (select count(1) from tb where a = t.a and c > t.c) + 1 from tb t
) m
group by a
union all
select a , 
       max(case px when 1 then b else null end) col1,
       max(case px when 2 then b else null end) col2,
       max(case px when 3 then b else null end) col3,
       max(case px when 4 then b else null end) col4,
       max(case px when 5 then b else null end) col5,
       max(case px when 6 then b else null end) col6,
       max(case px when 7 then b else null end) col7,
       max(case px when 8 then b else null end) col8,
       max(case px when 9 then b else null end) col9,
       max(case px when 10 then b else null end) col10
from
(
  select t.* , px = (select count(1) from tb where a = t.a and c > t.c) + 1 from tb t
) m
group by a
order by a , col1 

--sql 2005
select a , 
       max(case px when 1 then b else null end) col1,
       max(case px when 2 then b else null end) col2,
       max(case px when 3 then b else null end) col3,
       max(case px when 4 then b else null end) col4,
       max(case px when 5 then b else null end) col5,
       max(case px when 6 then b else null end) col6,
       max(case px when 7 then b else null end) col7,
       max(case px when 8 then b else null end) col8,
       max(case px when 9 then b else null end) col9,
       max(case px when 10 then b else null end) col10
from
(
  select t.* , px = row_number() over(partition by a order by c desc) from tb t
) m
group by a
union all
select a , 
       max(case px when 1 then b else null end) col1,
       max(case px when 2 then b else null end) col2,