两表合并的问题
表一：
　　　bh name sex
1001 aaa boy
1002 bbb girl
1003 ccc boy

表二：
　　bh name sex
1001 ddd boy
1005 eee boy
1004 fff girl

合并后：
　　bh name sex name sex
1001 aaa boy ddd boy
1002 bbb gril
1003 ccc boy
1004 fff girl
1005 eee boy

ＳＱＬ　请问怎么写？

[解决办法]
if object_id( 'pubs..表一 ') is not null
drop table 表一
go
create table 表一(bh varchar(10),name varchar(10),sex varchar(10))
insert into 表一(bh,name,sex) values( '1001 ', 'aaa ', 'boy ')
insert into 表一(bh,name,sex) values( '1002 ', 'bbb ', 'girl ')
insert into 表一(bh,name,sex) values( '1003 ', 'ccc ', 'boy ' )
go

if object_id( 'pubs..表二 ') is not null
drop table 表二
go
create table 表二(bh varchar(10),name varchar(10),sex varchar(10))
insert into 表二(bh,name,sex) values( '1001 ', 'ddd ', 'boy ')
insert into 表二(bh,name,sex) values( '1005 ', 'eee ', 'boy ')
insert into 表二(bh,name,sex) values( '1004 ', 'fff ', 'girl ' )
go

select * into test from
(
select * from 表一
union all
select * from 表二
) t
order by bh , name

declare @sql varchar(8000)
set @sql = 'select bh '
select @sql = @sql + ' , max(case cast(px as varchar) when ' ' ' + cast(px as varchar) + ' ' ' then name else ' ' ' ' end) name ' + cast(px as varchar)
+ ' , max(case cast(px as varchar) when ' ' ' + cast(px as varchar) + ' ' ' then sex else ' ' ' ' end) sex ' + cast(px as varchar)
from (select distinct px from (select px=(select count(1) from test where bh=a.bh and name <a.name)+1 , * from test a) t) as m
set @sql = @sql + ' from (select px=(select count(1) from test where bh=a.bh and name <a.name)+1 , * from test a) t group by bh '

exec(@sql)

drop table 表一,表二,test
/*
bh name1 sex1 name2 sex2
---------- ---------- ---------- ---------- ----------
1001 aaa boy ddd boy
1002 bbb girl
1003 ccc boy
1004 fff girl
1005 eee boy

*/