with cte(n,mod,row) as
(
    select number, (number-1)%5, (number-1)/5 from master..spt_values where type='P' and number between 1 and 1000
)
select * from cte t pivot (max(n) for mod in ([0],[1],[2],[3],[4])) p
/*
row         0           1           2           3           4
----------- ----------- ----------- ----------- ----------- -----------
0           1           2           3           4           5
1           6           7           8           9           10
2           11          12          13          14          15
3           16          17          18          19          20
...
197         986         987         988         989         990
198         991         992         993         994         995
199         996         997         998         999         1000
*/

------解决方案--------------------
1楼有点遐思，如果从3-1002....
with t(n) as
(
   select number from master..spt_values where type='P' and number between 3 and 1002
)
,t1 as (select *,n%5 as c from t)
, t2 as(select *,ROW_NUMBER() over(PARTITION BY c order by n) rownum from t1)
select [3] c1,[4] c2,[0] c3,[1] c4,[2] c5--这里决定顺序,如果要动态变，需要用动态SQL语句写查询改变查询顺序
from(select * from t2) src
pivot (max(n) for c in([0],[1],[2],[3],[4]))pvt


------解决方案--------------------

SQL code

select number, ((number-1)%5)+1 as title1, 
((number-1)/5)+1 as title2 into #test from master..spt_values 
where type='P' and number between 1 and 1000
select * from #test

declare @str varchar(max)
set @str=''
select @str=@str+','+'[col'+ltrim(title1)+']'+'=max(case when title1='+
QUOTENAME(title1,'''')+' then number else 0 end)' from #test 
group by title1
exec('select col1,col2,col3,col4,col5 from(
select title2'+@str+' from #test group by title2)a')
/*
col1    col2    col3    col4    col5
1    2    3    4    5
6    7    8    9    10
11    12    13    14    15
16    17    18    19    20
21    22    23    24    25
26    27    28    29    30
31    32    33    34    35
36    37    38    39    40
41    42    43    44    45
46    47    48    49    50
51    52    53    54    55
56    57    58    59    60
61    62    63    64    65
66    67    68    69    70
71    72    73    74    75
76    77    78    79    80
81    82    83    84    85
86    87    88    89    90
91    92    93    94    95
96    97    98    99    100
101    102    103    104    105
106    107    108    109    110
111    112    113    114    115
116    117    118    119    120
121    122    123    124    125
126    127    128    129    130
131    132    133    134    135
136    137    138    139    140
141    142    143    144    145
146    147    148    149    150
151    152    153    154    155
156    157    158    159    160
161    162    163    164    165
166    167    168    169    170
171    172    173    174    175
176    177    178    179    180
181    182    183    184    185
186    187    188    189    190
191    192    193    194    195
196    197    198    199    200
201    202    203    204    205
......
......
911    912    913    914    915
916    917    918    919    920
921    922    923    924    925
926    927    928    929    930
931    932    933    934    935
936    937    938    939    940
941    942    943    944    945
946    947    948    949    950
951    952    953    954    955
956    957    958    959    960
961    962    963    964    965
966    967    968    969    970
971    972    973    974    975
976    977    978    979    980
981    982    983    984    985
986    987    988    989    990
991    992    993    994    995
996    997    998    999    1000
*/