Aggregate Concatinate in join

Is table2 supposed to have a name column where a row in table 1 corresponds a
row in table 2 where the id's are equal?
try this it will create the table and populate it with your data from the
other tables.
select rtrim(a.name)+','+b.name as names, a.id as id, b.[date] as [date]
into table3 from table1 as a inner join table2 as b on (a.id=b.id)
if the other tables are being updated or used by apps then you can use a
view... create view as remove the 'into table3'
Hope it helps,
Netmon
"jobs" wrote:

> I have a two tables:
> table1
> id
> name
> table2
> id
> date
> I'd like to produce
> table3:
> id names date
> where names = all names for the id concatinated seperated by ","
>
Netmon wrote:

> Is table2 supposed to have a name column where a row in table 1 corresponds a
> row in table 2 where the id's are equal?
> try this it will create the table and populate it with your data from the
> other tables.
> select rtrim(a.name)+','+b.name as names, a.id as id, b.[date] as [date]
> into table3 from table1 as a inner join table2 as b on (a.id=b.id)
> if the other tables are being updated or used by apps then you can use a
> view... create view as remove the 'into table3'
> Hope it helps,
> Netmon
>
> "jobs" wrote:
>
[vbcol=seagreen]
In SQL Server 2005 you can do like this
select distinct a.id , stuff((select ','+name as [text()] from a as b
where a.id = b.id for xml path('')),1,1,'') as names,
b.date
from a inner join b on a.id = b.id
Regards
Amish Shah