if object_id('[tb]') is not null drop table [tb]
go
create table [tb]([A] int,[B] int,[C] int)
insert [tb]
select 1,1,15 union all
select 1,2,17 union all
select 1,3,12 union all
select 2,4,12 union all
select 2,5,16 union all
select 3,6,17 union all
select 3,7,13
go

;with t1 as(
select *,rn=row_number() over(partition by a order by c desc) from [tb]
)
select a,[1],[2],[3],[4],[5],[6],[7],[8],[9],[10]
from(
  select 1 as px2,a,max(case when rn=1 then c else 0 end) as [1],
  max(case when rn=2 then c else 0 end) as [2],
  max(case when rn=3 then c else 0 end) as [3],
  max(case when rn=4 then c else 0 end) as [4],
  max(case when rn=5 then c else 0 end) as [5],
  max(case when rn=6 then c else 0 end) as [6],
  max(case when rn=7 then c else 0 end) as [7],
  max(case when rn=8 then c else 0 end) as [8],
  max(case when rn=9 then c else 0 end) as [9],
  max(case when rn=10 then c else 0 end) as [10],
  sum(c) as px1
  from t1 group by a
  union all
  select 2,a,max(case when rn=1 then b else 0 end) as [1],
  max(case when rn=2 then b else 0 end) as [2],
  max(case when rn=3 then b else 0 end) as [3],
  max(case when rn=4 then b else 0 end) as [4],
  max(case when rn=5 then b else 0 end) as [5],
  max(case when rn=6 then b else 0 end) as [6],
  max(case when rn=7 then b else 0 end) as [7],
  max(case when rn=8 then b else 0 end) as [8],
  max(case when rn=9 then b else 0 end) as [9],
  max(case when rn=10 then b else 0 end) as [10],
  sum(c)
  from t1 group by a
) t
order by px1 desc,a,px2

/*
a           1           2           3           4           5           6           7           8           9           10
----------- ----------- ----------- ----------- ----------- ----------- ----------- ----------- ----------- ----------- -----------
1           17          15          12          0           0           0           0           0           0           0
1           2           1           3           0           0           0           0           0           0           0
3           17          13          0           0           0           0           0           0           0           0
3           6           7           0           0           0           0           0           0           0           0
2           16          12          0           0           0           0           0           0           0           0
2           5           4           0           0           0           0           0           0           0           0

(6 行受影响)

*/

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

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