34,838
社区成员




--创建一个临时表来实现你的要求.
create table tb(Xname varchar(10),Fname varchar(10),Tel varchar(10))
insert into tb values('张三', '张爸', '84595870')
insert into tb values('李四', '李爸', '84598456')
insert into tb values('刘五', '刘爸', '84599884')
insert into tb values('陈六', '陈爸', '84561865')
go
select px = identity(int,0,1) , * into tmp from tb
select item1,item2,
item3 = case when item4 = '' then '' else item3 end ,
item4,
item5 = case when item4 = '' then '' else item3 end ,
item6
from
(
select id = 1 , px1 ,
item1 = 'Xname',
max(case px2 when 0 then xname else '' end) item2,
item3 = 'Xname',
max(case px2 when 1 then xname else '' end) item4,
item5 = 'Xname',
max(case px2 when 2 then xname else '' end) item6
from
(
select px1 = px/3 , px2 = px%3 , * from tmp
) t
group by px1
union all
select id = 2 , px1 ,
item1 = 'Fname',
max(case px2 when 0 then Fname else '' end) item2,
item3 = 'Fname',
max(case px2 when 1 then Fname else '' end) item4,
item5 = 'Fname',
max(case px2 when 2 then Fname else '' end) item6
from
(
select px1 = px/3 , px2 = px%3 , * from tmp
) t
group by px1
union all
select id = 3 , px1 ,
item1 = 'Tel',
max(case px2 when 0 then tel else '' end) item2,
item2 = 'Tel',
max(case px2 when 1 then tel else '' end) item4,
item3 = 'Tel',
max(case px2 when 2 then tel else '' end) item6
from
(
select px1 = px/3 , px2 = px%3 , * from tmp
) t
group by px1
) m
order by px1, id
drop table tb,tmp
/*
item1 item2 item3 item4 item5 item6
----- ---------- ----- ---------- ----- ----------
Xname 张三 Xname 李四 Xname 刘五
Fname 张爸 Fname 李爸 Fname 刘爸
Tel 84595870 Tel 84598456 Tel 84599884
Xname 陈六
Fname 陈爸
Tel 84561865
(所影响的行数为 6 行)
*/
--参考此例,也是三个一组
开始
ID title picture
91 GS型泵 20071129035558.jpg
90 GS型泵 20071129035422.gif
86 GSD型 20071129034920.jpg
84 GSD型 20071129034716.gif
81 GEGS 20071129033721.jpg
78 GEGS 20071129033000.gif
77 GCCP 20071129032051.gif
结果
ID title picture ID1 title1 picture1 ID2 title2 picture2
91 GS型泵 1.jpg 90 GS型泵 2.gif 86 GSD型 3.jpg
84 GSD型 4.gif 81 GEGS 5.jpg 78 GEGS 6.gif
77 GCCP 7.gif
----------------------------------
[code=SQL]create table tb(ID int , title varchar(10), picture varchar(10))
insert into tb values(91, 'GS型泵', '1.jpg')
insert into tb values(90, 'GS型泵', '2.gif')
insert into tb values(86, 'GSD型' , '3.jpg')
insert into tb values(84, 'GSD型' , '4.gif')
insert into tb values(81, 'GEGS' , '5.jpg')
insert into tb values(78, 'GEGS' , '6.gif')
insert into tb values(77, 'GCCP' , '7.gif')
go
select t1.ID , t1.title , t1.picture ,
isnull(t2.ID,'') id1 , isnull(t2.title,'') title1 , isnull(t2.picture,'') picture2 ,
isnull(t3.ID,'') id2 , isnull(t3.title,'') title2 , isnull(t3.picture,'') picture3
from
(select * , id1 = (px - 1) / 3 from (SELECT * , px=(SELECT COUNT(id) FROM tb WHERE id > a.id) + 1 FROM tb a) t where (px - 1) % 3 = 0) t1
left join
(select * , id1 = (px - 1) / 3 from (SELECT * , px=(SELECT COUNT(id) FROM tb WHERE id > a.id) + 1 FROM tb a) t where (px - 1) % 3 = 1) t2
on t1.id1 = t2.id1
left join
(select * , id1 = (px - 1) / 3 from (SELECT * , px=(SELECT COUNT(id) FROM tb WHERE id > a.id) + 1 FROM tb a) t where (px - 1) % 3 = 2) t3
on t1.id1 = t3.id1
drop table tb
/*
ID title picture id1 title1 picture2 id2 title2 picture3
----------- ---------- ---------- ----------- ---------- ---------- ----------- ---------- ----------
91 GS型泵 1.jpg 90 GS型泵 2.gif 86 GSD型 3.jpg
84 GSD型 4.gif 81 GEGS 5.jpg 78 GEGS 6.gif
77 GCCP 7.gif 0 0
(所影响的行数为 3 行)
*/