坚排转横排问题??

[kang123033]

A表:
Xname Fname Tel
张三 张爸 84595870
李四 李爸 84598456
刘五 刘爸 84599884
陈六 陈爸 84561865
.... .... .........

转换成B表:
Xname 张三 Xname 李四 Xname 刘五
Fname 张爸 Fname 李爸 Fname 刘爸
Tel [phone] Tel [phone]爸 Tel [phone]
Xname 陈六
Fname 陈爸
Tel [phone]

意思:将A表的坚向转为B表的,B表共有6列...十分感谢

[kk19840210]

那要看你有多少数据了

每个表至多可定义 1024 列

[dawugui]

这是啥意思?三个一组进行转换?

[dawugui]

--参考此例,也是三个一组
开始 
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 行）
*/

--这个数值ID转换为字符串,就为空的0不显示出来
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 cast(t1.ID as varchar) id, t1.title , t1.picture ,
isnull(cast(t2.ID as varchar),'') id1 , isnull(t2.title,'') title1 , isnull(t2.picture,'') picture2 ,
isnull(cast(t3.ID as varchar),'') 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

（所影响的行数为 3 行）
*/[/code]

[kang123033]

楼上的这个结果不是我想要的那种,谢谢

[dawugui]

你这个不就是三个一组的横向显示吗?

[yxhly]

留个记号

[dawugui]

--创建一个临时表来实现你的要求.
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   [phone]   Tel   [phone]   Tel   [phone]
Xname 陈六                                
Fname 陈爸                                
Tel   [phone]                          

（所影响的行数为 6 行）

*/