create table #s([id] int,a1 int,a2 int,a3 int,a4 int,a5 int,a6 int,a7 int,a8 int) insert into #s select 1,4345,234,45434,5676,778,2334,767,342 insert into #s select 2,234,345,878,23,67,787,453,4545create table #T([id] int,col int)insert into #T select id,a1 from #s union all select id,a2 from #s union all select id,a3 from #s union all select id,a4 from #s union all select id,a5 from #s union all select id,a6 from #s union all select id,a7 from #s union all select id,a8 from #sselect c.id, a1 = (select a.col from #T a,#T b where a.id=b.id and a.col>=b.col and a.id=c.id group by a.col having count(b.id)=1), a2 = (select a.col from #T a,#T b where a.id=b.id and a.col>=b.col and a.id=c.id group by a.col having count(b.id)=2), a3 = (select a.col from #T a,#T b where a.id=b.id and a.col>=b.col and a.id=c.id group by a.col having count(b.id)=3), a4 = (select a.col from #T a,#T b where a.id=b.id and a.col>=b.col and a.id=c.id group by a.col having count(b.id)=4), a5 = (select a.col from #T a,#T b where a.id=b.id and a.col>=b.col and a.id=c.id group by a.col having count(b.id)=5), a6 = (select a.col from #T a,#T b where a.id=b.id and a.col>=b.col and a.id=c.id group by a.col having count(b.id)=6), a7 = (select a.col from #T a,#T b where a.id=b.id and a.col>=b.col and a.id=c.id group by a.col having count(b.id)=7), a8 = (select a.col from #T a,#T b where a.id=b.id and a.col>=b.col and a.id=c.id group by a.col having count(b.id)=8) from #T c group by c.id drop table #T,#S
insert into #s select 1,4345,234,45434,5676,778,2334,767,342
insert into #s select 2,234,345,878,23,67,787,453,4545create table #T([id] int,col int)insert into #T
select id,a1 from #s
union all select id,a2 from #s
union all select id,a3 from #s
union all select id,a4 from #s
union all select id,a5 from #s
union all select id,a6 from #s
union all select id,a7 from #s
union all select id,a8 from #sselect
c.id,
a1 = (select a.col from #T a,#T b where a.id=b.id and a.col>=b.col and a.id=c.id group by a.col having count(b.id)=1),
a2 = (select a.col from #T a,#T b where a.id=b.id and a.col>=b.col and a.id=c.id group by a.col having count(b.id)=2),
a3 = (select a.col from #T a,#T b where a.id=b.id and a.col>=b.col and a.id=c.id group by a.col having count(b.id)=3),
a4 = (select a.col from #T a,#T b where a.id=b.id and a.col>=b.col and a.id=c.id group by a.col having count(b.id)=4),
a5 = (select a.col from #T a,#T b where a.id=b.id and a.col>=b.col and a.id=c.id group by a.col having count(b.id)=5),
a6 = (select a.col from #T a,#T b where a.id=b.id and a.col>=b.col and a.id=c.id group by a.col having count(b.id)=6),
a7 = (select a.col from #T a,#T b where a.id=b.id and a.col>=b.col and a.id=c.id group by a.col having count(b.id)=7),
a8 = (select a.col from #T a,#T b where a.id=b.id and a.col>=b.col and a.id=c.id group by a.col having count(b.id)=8)
from
#T c
group by
c.id
drop table #T,#S