34,588
社区成员
发帖
与我相关
我的任务
分享
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 行受影响)
*/