一个script

[ktoya]

result
1
2
3
4
5
6
7
8
9
10

想把他变成3列挨个排下来，如上面的表把他变成
c1 c2 c3
1 2 3
4 5 6
7 8 9
10

[Andy__Huang]

declare @s varchar(8000) set @s='' select @s=@s+CAST(number as varchar)+CHAR(9)+case when number %3=0 then CHAR(13) else CHAR(9) end from master..spt_values where type='P' and number between 1 and 20 print left(@s,len(@s)-1) /* 1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 */

[---涛声依旧---]

[Leon_He2014]

select 1 result 
into #t
union all select 2
union all select 3
union all select 4
union all select 5
union all select 6
union all select 7
union all select 8
union all select 9
union all select 10

select c1=max( case when result%3=1 then result else null end )
,c2=max( case when result%3=2 then result else null end )
,c3=max( case when result%3=0 then result else null end )
from #t
group by (result-1)/3

[ktoya]

对不起，问题补充，并不是1，2，3，4这样排下来的。有可能是这样 -1 -1 -1 1 -1 1 -1 -1 -1 一共9个数，那么就排成 -1，-1，-1 1，-1，-1 -1，-1，-1

[---涛声依旧---]

请问楼主这些数是存放在一个表里的吗？

[KeepSayingNo]

这个放到代码里面去做多好啊，这不是折腾DBA吗

[唐诗三百首]

create table kt(result int)

insert into kt
 select -1 union all
 select -1 union all
 select -1 union all
 select 1 union all
 select -1 union all
 select 1 union all
 select -1 union all
 select -1 union all
 select -1


with t as
(select result,
        (row_number() over(order by getdate())-1)/3 'rn'
 from kt
)
select [1] 'a',[2] 'b',[3] 'c'
from
(select result,rn,row_number() over(order by getdate())-3*rn 'rn2'
 from t) a
pivot(max(result) for rn2 in([1],[2],[3])) p

/*
a           b           c
----------- ----------- -----------
-1          -1          -1
1           -1          1
-1          -1          -1

(3 row(s) affected)
*/

[唐诗三百首]

create table kt(result int)

insert into kt
 select 1 union all
 select 2 union all
 select 3 union all
 select 4 union all
 select 5 union all
 select 6 union all
 select 7 union all
 select 8 union all
 select 9 union all
 select 10


with t as
(select result,
        (row_number() over(order by getdate())-1)/3 'rn'
 from kt
)
select [1] 'a',[2] 'b',[3] 'c'
from
(select result,rn,row_number() over(order by getdate())-3*rn 'rn2'
 from t) a
pivot(max(result) for rn2 in([1],[2],[3])) p

/*
a           b           c
----------- ----------- -----------
1           2           3
4           5           6
7           8           9
10          NULL        NULL

(4 row(s) affected)
*/

[Shawn]

;with t as
(
	select result, (row_number() over(order by getdate())-1)/3 'rn'
	from kt
)
select [1] 'a',[2] 'b',[3] 'c'
FROM
(
	select result,rn,row_number() over(PARTITION BY rn order by rn) 'rn2'	--这里可以直接用partition by
	from t
) a
PIVOT
(
	max(result) for rn2 in([1],[2],[3])
) b