横向排序，还是有点问题？
重列原始数据表a，代码如下：

create table tb(id int,t text,a1 int,a2 int,a3 int)
insert into tb values(1,'ta',0,1,9)
insert into tb values(2,'tc',4,8,4) 
insert into tb values(3,'tt',7,3,3)
insert into tb values(4,'rt',3,0,3)
insert into tb values(5,'gg',7,3,6)
insert into tb values(6,'sr',2,5,5)
insert into tb values(7,'qq',4,5,9)
insert into tb values(8,'sd',5,5,8)
insert into tb values(9,'vs',5,0,4)
insert into tb values(10,'ws',4,0,8)
insert into tb values(11,'rf',9,3,4)
go

要求对数据表a进行横向排序。id,t字段不进行排序，只
对a1,a2,a3字段按从小到大进行横向排序，所排的结果输
入到表b中。表b的结构如下：

id text a1 a2 a3 b1 b2 b3
1 'ta' 0 1 9 0 1 9
2 'tc' 4 8 4 4 4 8
3 'tt' 7 3 3 3 3 7
4 'rt' 3 0 3 0 3 3
5 'gg' 7 3 6 3 6 7
6 'sr' 2 5 5 2 5 5
7 'qq' 4 5 9 4 5 9
8 'sd' 5 5 8 5 5 8
9 'vs' 5 0 4 0 4 5
10 'ws' 4 0 8 0 4 8
11 'rf' 9 3 4 3 4 9

下面贴上两个有点问题代码，原程序都测试通过了。
只不过采用我上面表a的数据时，当然没有text字段，并
增加了a4字段，值全为0，进行测试时，出现了数据不对
并排列混乱id也混乱的情况。但原程序测试都对的，不
知道问题出在哪儿？
--------------------------
第一个程序如下：

create table tb(id int, a1 int, a2 int, a3 int , a4 int)
insert into tb values(1, 5 , 10, 23, 1 )
insert into tb values(2, 34, 56, 7 , 17) 
insert into tb values(3, 14, 28, 77, 25)
go
--用个临时表
select * into tmp from
(
  select id , a1 from tb
  union all
  select id , a2 a1 from tb
  union all
  select id , a3 a1 from tb
  union all
  select id , a4 a1 from tb
) t

select t1.* , t2.a1 b1,t2.a2 b2,t2.a3 b3,t2.a4 b4 from tb t1,
(
  select id , 
  max(case px when 1 then a1 else 0 end) a1,
  max(case px when 2 then a1 else 0 end) a2,
  max(case px when 3 then a1 else 0 end) a3,
  max(case px when 4 then a1 else 0 end) a4
  from
  (
  select px=(select count(1) from tmp where id = m.id and a1 < m.a1)+1 , * from tmp m
  ) t
  group by id
) t2
where t1.id = t2.id

drop table tb,tmp

/*
id a1 a2 a3 a4 b1 b2 b3 b4  
----------- ----------- ----------- ----------- ----------- ----------- ----------- ----------- ----------- 
1 5 10 23 1 1 5 10 23
2 34 56 7 17 7 17 34 56
3 14 28 77 25 14 25 28 77

（所影响的行数为 3 行）
*/
--------------------
第二个程序如下：

create table # (id int, a1 int, a2 int, a3 int, a4 int)
insert # select 1, 5, 10, 23, 1 
insert # select 2, 34, 56, 7, 17 
insert # select 3, 14, 28, 77, 25 

go

select ID,[COL]=a1,row=1 into #1
from #
union all 
select ID,a2,row=1 from #
union all
select ID,a3,row=1 from #
union all
select ID,a4,row=1 from #
order by ID,COL asc
go
declare @i int,@j int
update #1
set @i=case when ID=@j then @i+1 else 1 end,row=@i,@j=ID
go

declare @s nvarchar(2000)
set @s='select ID'
select @s=@s+',[b'+rtrim(row)+']=max(case when row='+rtrim(row)+' then COl else 0 end)'
from #1 group by Row
exec(@s+' from #1 group by ID')
ID b1 b2