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

坚排转横排问题??

kang123033 2007-12-11 03:41:03
A表:
Xname Fname Tel
张三 张爸 84595870
李四 李爸 84598456
刘五 刘爸 84599884
陈六 陈爸 84561865
.... .... .........

转换成B表:
Xname 张三 Xname 李四 Xname 刘五
Fname 张爸 Fname 李爸 Fname 刘爸
Tel 84595870 Tel 84598456爸 Tel 84599884
Xname 陈六
Fname 陈爸
Tel 84561865

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


...全文
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
那要看你有多少数据了

每个表至多可定义 1024 列
回复
发动态
发帖子
MS-SQL Server
创建于2007-09-28

3.2w+

社区成员

MS-SQL Server相关内容讨论专区
申请成为版主
社区公告
暂无公告