求高手 拆分数据最简单的SQL语句

[qq497525725]

将一列数据 分3列显示如
id
1
2
3
4
5
6
7
8
9
10

查询后
id1 id2 id3
1 2 3
4 5 6
........

[jwwyqs]

create table s(a int)
insert into s select 1 union select 2 union select 3 union select 4 union select 5 union select 6
union select 7 union select 8 union select 9 union select 10 union select 11

select * from s

select
max(case when a%3=1 then a else 0 end) a,
max(case when a%3=2 then a else 0 end) b,
max(case when a%3=0 then a else 0 end) c
from s
group by (a-1)/3
/*
1 2 3
4 5 6
7 8 9
10 11 0
*/

[SQL77]

create table s(a int) 
insert into s select 1 union select 2  union select 3  union select 4  union select 5  union select 6 
union select 7 union select 8  union select 9  union select 10  union select 11 

select * from s 

select 
max(case when (a-1)%3=0 then a else 0 end) a, 
max(case when (a-1)%3=1 then a else 0 end) b, 
max(case when (a-1)%3=2 then a else 0 end) c 
from s 
group by (a-1)/3 
a           b           c           
----------- ----------- ----------- 
1           2           3
4           5           6
7           8           9
10          11          0

（所影响的行数为 4 行）

[pt1314917]

--> 测试数据: [tb]
if object_id('[tb]') is not null drop table [tb]
create table [tb] (id int)
insert into [tb]
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

select id1=max(case xh when 1 then id else 0 end),
id2=max(case xh when 2 then id else 0 end),
id3=max(case xh when 3 then id else 0 end)
from
(select px=case id%3 when 0 then id/3-1 else id/3 end,xh=case id%3 when 0 then 3 else id%3 end,* from [tb])a
group by px


--结果：
id1         id2         id3
----------- ----------- -----------
1           2           3
4           5           6
7           8           9
10          0           0

[jwwyqs]

select
max(case when a%3=1 then a else 0 end) a,
max(case when a%3=2 then a else 0 end) b,
max(case when a%3=0 then a else 0 end) c
from s
group by (a-1)/3
/*
1 2 3
4 5 6
7 8 9
10 11 0
*/
select
c1=a.a,c2=b.a,c3=c.a
from s a
left join s b on b.a=a.a+1
left join s c on c.a=a.a+2
where (a.a-1)%3=0
/*
1 2 3
4 5 6
7 8 9
10 11 NULL
*/
select
max(case when (a-1)/4=0 then a else 0 end) a,
max(case when (a-1)/4=1 then a else 0 end) b,
max(case when (a-1)/4=2 then a else 0 end) c
from s
group by (a-1)%4
/*
1 5 9
2 6 10
3 7 11
4 8 0
*/

[xman_78tom]

select sum(case when (number-1)%3=0 then number else 0 end) id1,
sum(case when (number-1)%3=1 then number else 0 end) id2,
sum(case when (number-1)%3=2 then number else 0 end) id3
from (select number,grp=(number-1)/3
from master.dbo.spt_values where type='p' and number>=1) t
group by grp
/*
1	2	3
4	5	6
7	8	9
10	11	12
13	14	15
16	17	18
。。。
*/

[qq497525725]

多谢了 呵呵 结贴去

[pt1314917]

--如果ID不是连续的。
--> 测试数据: [tb]
if object_id('[tb]') is not null drop table [tb]
create table [tb] (id int)
insert into [tb]
select 1 union all
select 5 union all
select 2 union all
select 8 union all
select 9 union all
select 23 union all
select 54 union all
select 7 union all
select 12 union all
select 34

select px=identity(int,1,1),* into # from tb

select id1=max(case px%3 when 1 then id else 0 end),
id2=max(case px%3 when 2 then id else 0 end),
id3=max(case px%3 when 0 then id else 0 end)
from #
group by case px%3 when 0 then px/3-1 else px/3 end


--结果：
id1         id2         id3
----------- ----------- -----------
1           5           2
8           9           23
54          7           12
34          0           0