SQL table association problem

wingseeker

Table A
id name
1 aa
2 bb
3 cc

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

Want to get the following records
id aa bb cc
1 aaa 0 0
2 bbb 0 0
3 0 ccc 0
4 0 ddd 0
5 0 eee 0
Can the SQL query be completed? Thank you for your advice ~

trampzz

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

wingseeker

Thank you! !! !!