• 主页
• 基础类
• 应用实例
• 新技术前沿

# 坚排转横排问题??

kang123033 2007-12-11 03:41:03
A表:
Xname Fname Tel

.... .... .........

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

...全文
71 点赞 收藏 7

7 条回复

dawugui 2007-12-11
``````--创建一个临时表来实现你的要求.
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 行）

*/
``````

yxhly 2007-12-11

dawugui 2007-12-11

kang123033 2007-12-11

dawugui 2007-12-11
``````--参考此例,也是三个一组

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]

dawugui 2007-12-11

kk19840210 2007-12-11

MS-SQL Server

3.2w+

MS-SQL Server相关内容讨论专区