create table A(id int identity(1,1), [name] char(2))
insert A select 'aa'
insert A select 'bb'
insert A select 'cc'create table B(id int identity(1,1), Aid int, [value] char(3))
insert B select 1,'aaa'
insert B select 1,'bbb'
insert B select 2,'ccc'
insert B select 2,'ddd'
insert B select 2,'eee'declare @column varchar(8000)
select @column='select id,'select @column=@column+'case Aid when '+convert(varchar,id)+' then [value] else ''0'' end ['+[name]+'],' from A
set @column=stuff(@column,len(@column),1,'')+' from B '
exec (@column)
/*
id aa bb cc
----------- ---- ---- ----
1 aaa 0 0
2 bbb 0 0
3 0 ccc 0
4 0 ddd 0
5 0 eee 0*/drop table A
drop table B
insert A select 'aa'
insert A select 'bb'
insert A select 'cc'create table B(id int identity(1,1), Aid int, [value] char(3))
insert B select 1,'aaa'
insert B select 1,'bbb'
insert B select 2,'ccc'
insert B select 2,'ddd'
insert B select 2,'eee'declare @column varchar(8000)
select @column='select id,'select @column=@column+'case Aid when '+convert(varchar,id)+' then [value] else ''0'' end ['+[name]+'],' from A
set @column=stuff(@column,len(@column),1,'')+' from B '
exec (@column)
/*
id aa bb cc
----------- ---- ---- ----
1 aaa 0 0
2 bbb 0 0
3 0 ccc 0
4 0 ddd 0
5 0 eee 0*/drop table A
drop table B
解决方案 »
免费领取超大流量手机卡,每月29元包185G流量+100分钟通话, 中国电信官方发货