Aggregate Concatinate in join

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 ","jobs
You have two ID columns ,which one you want?
select tb1 .id,name,date from tb1 join tb2 on tb1.id=tb2.id
where name in ('a','b','c')
"jobs" <jobs@.webdos.com> wrote in message
news:1162310209.501090.68440@.e3g2000cwe.googlegroups.com...
>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 ","
>|||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:
>
> > 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 ","
> >
> >
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