坚排转横排问题??

--创建一个临时表来实现你的要求.

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 行）

*/