求2个表之间数据转换的问题

[gunblaze]

就是把4条2列记录转为1条8列记录
A表: dpt name B表: dtp1 name1 dpt2 name2 dpt3 name3 dpt4 name4
1 AA 1 AA 2 BB 3 CC 4 DD
2 BB
3 CC
4 DD

大致就是要把A表的记录(记录数<=4)填入B表，按顺序填，如果不满4条，则后面填空格
用SQL实现应该怎么做？

[LamarChen]

insert into table_b(dtp1,name1,dpt2,name2,dpt3,name3,dpt4,name4)
select table_a.dpt,table_a.name from table_a where .....
left join ... on ...

用这种形式吧，

[gxlineji]

insert into table_b
(select a.dpt,aa.name,bb.dpt ,bb.name,cc.dpt,cc.name,dd.dpt,dd.name from
(select * from a where dpt=1) aa
left join a bb on bb.dpt=2
left join a cc on cc.dpt=3
left join a dd on dd.dpt=4
)

[gxlineji]

用这个比较好!!
insert into table_b
select max(decode(dpt,1,dpt,'')) dpt1,max(decode(dpt,1,name,'')) name1,
max(decode(dpt,2,dpt,'')) dpt2,max(decode(dpt,2,name,'')) name2,
max(decode(dpt,3,dpt,'')) dpt3,max(decode(dpt,3,name,'')) name3,
max(decode(dpt,4,dpt,'')) dpt4,max(decode(dpt,4,name,'')) name4 from a