求：把N行记录转为一行记录

[ReViSion]

如题：
比如说，A表有字段 ID，Name,Pro三个字段
　　　select top 100 * from A 也能找出１００条记录
　　　我想要的结果是：
　　　每１０行记录转为一行记录如下格式
　　　ID1,Name1,Pro1,ID2,Name2,Pro2,ID3,Name3,Pro3..............Id10,Name10,Pro10
得到一个上面格式的１０行记录
　　　怎么整呀？？？？？？？？？？？？
（字段并不是固定的）

[ReViSion]

自己先顶一下

[rea1gz]

临时表方法：

select top 100 identity(int,1,1) as id1,* into #t from A

select
max(case when id1 % 10 =1 then id else null end) as id1,
max(case when id1 % 10 =1 then name else null end) as name1,
max(case when id1 % 10 =2 then id else null end) as id2,
max(case when id1 % 10 =2 then name else null end) as name2,
...
max(case when id1 % 10 =9 then id else null end) as id9,
max(case when id1 % 10 =9 then name else null end) as name9,
max(case when id1 % 10 =0 then id else null end) as id10,
max(case when id1 % 10 =0 then name else null end) as name10
from #t

drop table #t

[rea1gz]

也许有不用临时表的方法，但是表必须有主键，再说吧

[ReViSion]

我用VB写了一个，有点慢
不过我想全部用SQL语句能不能实现
我用的是数组
dim S
set s=Rs.getrows()

[ReViSion]

大家想想办法呀

[Student02370236]

先把记录读到游标里边，再把游标里的字段依次读出，然后用字符串连接的方法处理，也可以不用游标，直接把表里的数据读出来进行字符串连接。用TRAN－SQL完成

[ReViSion]

会太慢了吧，就没人懂吗？

[ReViSion]

各位大哥大姐，帮帮忙呀

[ReViSion]

没人理

[chichunhua]

mark

[yjbnew]

用游标听说很慢的?

[ReViSion]

就是呀，太慢了

[ReViSion]

自己UP下先

[zlj113]

帮你UP

[ReViSion]

有没有办法，没有办法也出个声呀，
我也好不傻傻等啦

[mengzulin]

效率最高的方法是用＂select top 100 * from A＂在读结果集时转换．

[ReViSion]

mengzulin(Julian) ( )
能不能说个具体点的想法

[filebat]

--测试数据
if object_id('ta') is not null drop table ta
go
create table ta(id int, name varchar(10), pro int)
insert ta select 1, 'abc', 1
union all select 1, 'abc', 1
union all select 1, 'abc', 1
union all select 1, 'abc', 1
union all select 1, 'abc', 1
union all select 1, 'abc', 1
union all select 1, 'abc', 1
union all select 1, 'abc', 1
union all select 1, 'abc', 1
union all select 1, 'abc', 1
union all select 2, '123', 2
union all select 2, '123', 2
union all select 2, '123', 2
union all select 2, '123', 2
select* from ta
--查询过程
select top 100 identity(int,1,1) as tid,* into #t from ta

select t1.id as id1, t1.name as name1, t1.pro as pro1
,t2.id as id2, t2.name as name2, t2.pro as pro2
,t3.id as id3, t3.name as name3, t3.pro as pro3
,t4.id as id4, t4.name as name4, t4.pro as pro4
,t5.id as id5, t5.name as name5, t5.pro as pro5
,t6.id as id6, t6.name as name6, t6.pro as pro6
,t7.id as id7, t7.name as name7, t7.pro as pro7
,t8.id as id8, t8.name as name8, t8.pro as pro8
,t9.id as id9, t9.name as name9, t9.pro as pro9
,t10.id as id10, t10.name as name10, t10.pro as pro10
from (select * from #t where (tid-1)%10=0)t1
left join(select * from #t where (tid-1)%10=1)t2 on t2.tid=t1.tid+1
left join(select * from #t where (tid-1)%10=2)t3 on t3.tid=t2.tid+1
left join(select * from #t where (tid-1)%10=3)t4 on t4.tid=t3.tid+1
left join(select * from #t where (tid-1)%10=4)t5 on t5.tid=t4.tid+1
left join(select * from #t where (tid-1)%10=5)t6 on t6.tid=t5.tid+1
left join(select * from #t where (tid-1)%10=6)t7 on t7.tid=t6.tid+1
left join(select * from #t where (tid-1)%10=7)t8 on t8.tid=t7.tid+1
left join(select * from #t where (tid-1)%10=8)t9 on t9.tid=t8.tid+1
left join(select * from #t where (tid-1)%10=9)t10 on t10.tid=t9.tid+1
--扫尾
drop table #t
drop table ta

[ReViSion]

也不失为一种办法，就是数据量大时，速度可能真有点不近人意