重列原始数据表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
) tselect 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.iddrop 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 goselect 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
godeclare @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 b3 b4
----------- ----------- ----------- ----------- -----------
1 1 5 10 23
2 7 17 34 56
3 14 25 28 77
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
) tselect 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.iddrop 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 goselect 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
godeclare @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 b3 b4
----------- ----------- ----------- ----------- -----------
1 1 5 10 23
2 7 17 34 56
3 14 25 28 77
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) goselect ID,[COL]=a1,row=1 into #1
from tb
union all
select ID,a2,row=1 from tb
union all
select ID,a3,row=1 from tb
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+' into ##T ---生成临时表
from #1 group by ID') go
select
t.*,t2.b1,b2,b3
from
tb t
join
##T t2 on t.ID=t2.ID
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
--用个临时表
select id1 = identity(int,1,1) , * into tmp from
(
select id , t , a1 from tb
union all
select id , t , a2 a1 from tb
union all
select id , t , a3 a1 from tb
) t
order by id , a1 select t1.* , t2.a1 b1,t2.a2 b2,t2.a3 b3 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
from
(
select px=(select count(1) from tmp where (id = m.id and a1 < m.a1) or ((id = m.id and a1 = m.a1 and id1 < m.id1)))+1 , * from tmp m
) t
group by id
) t2
where t1.id = t2.id drop table tb,tmp /*
id t 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（所影响的行数为 11 行）*/