有一个表有两个字段。
number name
1001 jack
............
1021 teddy
.............
1041 mary
.............
1061 jacky
.............
1081 james
查询出来结果
number name number name number name ......
1001 jack 1021 teddy 1041 mary
这里是假如有100条数据,以后会增加的,就是每20行换一次,如此类推,不用管排序
------解决方案--------------------
declare @s varchar(8000)
set @s=''
select @s=@s+name+CHAR(9)+case when rn%20=0 then CHAR(13) else CHAR(9) end
from (select *,ROW_NUMBER() over(order by number) as rn from tb)t
print left(@s,len(@s)-1)
------解决方案--------------------
这是100条分折十行的结果,自己参考着写就可以了
if OBJECT_ID('tb')>0 drop table tb
go
create table tb(number int,name varchar(20))
insert tb
select top 100 rn1=ROW_NUMBER()over(order by getdate()),ROW_NUMBER()over(order by getdate())
from master..spt_values
;with t as
(
select rn1=(ROW_NUMBER()over(order by getdate())-1)%10+1,
rn2=(ROW_NUMBER()over(order by getdate())-1)/10+1,
*
from tb
)
select max(case when rn1=1 then number else '' end) number,
max(case when rn1=1 then name else '' end) name,
max(case when rn1=2 then number else '' end) number,
max(case when rn1=2 then name else '' end) name,
max(case when rn1=3 then number else '' end) number,
max(case when rn1=3 then name else '' end) name,
max(case when rn1=4 then number else '' end) number,
max(case when rn1=4 then name else '' end) name,