求1SQL语句。难！

[renaski]

use pubs
go
declare @ss nvarchar(4000)
set @ss = ''
select top 5 @ss = @ss +' ' + convert(nvarchar(4000),job_id) from jobs
select stuff(@ss,1,1,'') as 结果


我现在想得到的结果是
1 2 3 4 5
6 7 8 9 10
11 12 13 14
就是每隔5个数据换一行，前5个数据合并成1个字段。

谢谢！

[xieye]

有点不明白，前5个数据合并成一个字段，后面的数据怎么处理？

[renaski]

后面的数据，每5个合并成1个字段
现在的结果是1 2 3 4 5
想要的是
1 2 3 4 5
6 7 8 9 10
11 12 13 14

[xieye]

一个笨办法，呵呵

use pubs
go
declare @ss nvarchar(4000)
declare @tt nvarchar(4000)
set @ss=''

declare @i int
set @i=1

declare cur_ss cursor for
select job_id from jobs

open cur_ss
fetch next from cur_ss into @tt

while @@fetch_status=0
begin
while @i<=5
begin
set @ss = @ss +' ' + @tt
fetch next from cur_ss into @tt
if @@fetch_status<>0 break
set @i=@i+1
end

select stuff(@ss,1,1,'') as 结果

set @i=1
set @ss=''
end


close cur_ss

deallocate cur_ss

[xieye]

还是用个临时表吧：

use pubs
go
declare @ss nvarchar(4000)
declare @tt nvarchar(4000)
set @ss=''

declare @i int
set @i=1

declare cur_ss cursor for
select job_id from jobs

create table #tmp (结果 nvarchar(4000))

open cur_ss
fetch next from cur_ss into @tt

while @@fetch_status=0
begin
while @i<=5
begin
set @ss = @ss +' ' + @tt
fetch next from cur_ss into @tt
if @@fetch_status<>0 break
set @i=@i+1
end

insert into #tmp values(stuff(@ss,1,1,''))

set @i=1
set @ss=''
end


close cur_ss
deallocate cur_ss

select * from #tmp

drop table #tmp

[renaski]

...

[renaski]

up