SQL表关联问题
表 A
id name
1 aa
2 bb
3 cc

表 B
id Aid value
1 1 aaa
2 1 bbb
3 2 ccc
4 2 ddd
5 2 eee

现要得到如下记录
id aa bb cc
1 aaa 0 0
2 bbb 0 0
3 0 ccc 0
4 0 ddd 0
5 0 eee 0
SQL查询语句能够完成吗，高手指点下，感激不尽~


[解决办法]
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