Query

I have two tables
Table 1 is as following
Id
1
2
3
Table 2 is as following. Here Id is the foreign key.
Id Name
1 David
1 James
1 Larry
2 Smith
2 Will
How can i write a query joing two tables, which can return result as followi
ng
Id Name
1 David,James,Larry
2 Smith,Will
ThanksCheck whether the below given script gives ur desired output or not
create table sample_name(
name_id int primary key)
go
insert into sample_name values(1)
insert into sample_name values(2)
insert into sample_name values(3)
go
create table sample_name_sub(
name_id int foreign key references sample_name(name_id),
cus_name varchar(50)
)
go
insert into sample_name_sub values(1,'David')
insert into sample_name_sub values(1,'James')
insert into sample_name_sub values(1,'Kelly')
insert into sample_name_sub values(2,'smith')
insert into sample_name_sub values(2,'Welly')
go
create function fn_return_names(@.id int)
returns varchar(500)
as
Begin
declare @.name varchar(500)
set @.name = ''
select @.name = @.name + cus_name + ','
from sample_name_sub where name_id = @.id
if len(@.name) >= 1
select @.name = substring(@.name,1,len(@.name)-1)
return @.name
End
go
select name_id,
dbo.fn_return_names(name_id)
from sample_name
Regards
Sudarshan
"mvp" wrote:

> I have two tables
> Table 1 is as following
> Id
> 1
> 2
> 3
> Table 2 is as following. Here Id is the foreign key.
> Id Name
> 1 David
> 1 James
> 1 Larry
> 2 Smith
> 2 Will
> How can i write a query joing two tables, which can return result as follo
wing
> Id Name
> 1 David,James,Larry
> 2 Smith,Will
> Thanks|||Also refer this to know why you need function
[url]http://sqljunkies.com/WebLog/amachanic/archive/2004/11/10/5065.aspx?Pending=true[/
url]
Madhivanan