100条数据，如何分每成20行*N列显示出来
有一个表有两个字段。
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,
max(case when rn1=5 then number else '' end) number,
max(case when rn1=5 then name else '' end) name,
max(case when rn1=6 then number else '' end) number,
max(case when rn1=6 then name else '' end) name,
max(case when rn1=7 then number else '' end) number,
max(case when rn1=7 then name else '' end) name,
max(case when rn1=8 then number else '' end) number,
max(case when rn1=8 then name else '' end) name,
max(case when rn1=9 then number else '' end) number,
max(case when rn1=9 then name else '' end) name,
max(case when rn1=10 then number else '' end) number,
max(case when rn1=10 then name else '' end) name
from t
group by rn2
/*
number name number name number name number name number name number name number name number name number name number name
----------- -------------------- ----------- -------------------- ----------- -------------------- ----------- -------------------- ----------- -------------------- ----------- -------------------- ----------- -------------------- ----------- -------------------- ----------- -------------------- ----------- --------------------
1 1 2 2 3 3 4 4 5 5 6 6 7 7 8 8 9 9 10 10
11 11 12 12 13 13 14 14 15 15 16 16 17 17 18 18 19 19 20 20
21 21 22 22 23 23 24 24 25 25 26 26 27 27 28 28 29 29 30 30
31 31 32 32 33 33 34 34 35 35 36 36 37 37 38 38 39 39 40 40
41 41 42 42 43 43 44 44 45 45 46 46 47 47 48 48 49 49 50 50
51 51 52 52 53 53 54 54 55 55 56 56 57 57 58 58 59 59 60 60
61 61 62 62 63 63 64 64 65 65 66 66 67 67 68 68 69 69 70 70
71 71 72 72 73 73 74 74 75 75 76 76 77 77 78 78 79 79 80 80
81 81 82 82 83 83 84 84 85 85 86 86 87 87 88 88 89 89 90 90
91 91 92 92 93 93 94 94 95 95 96 96 97 97 98 98 99 99 100 100
(10 行受影响)
*/
