Merging Records within SQL

[GrahamDuffield]

PLEASE HELP !

I have two tables, and need to pull info from both. However, table 2 will have MANY records for each unique row in table 1, and I need to say something like

"Show me name and address from table 1"

and combine all the info. from the NOTES field in table two, for all records where the customer id is that in table 1.

i.e.

Table 1

Sarah
Michael
Paul

Table 2

Sarah Smoker
Sarah Likes Bananas
Sarah Hates Fish
Michael Likes Red
Michael Enjoys Travel
Paul Supports Leeds

Resulting output

Sarah "Smoker : Likes Bananas : Hates Fish"
Michael "Likes Red : Enjoys Travel"
Paul "Supports Leeds"

Hope someone can help me !!!!!

[MAK]

If it is SQL server 2000, you can do this by creating a function like below

use pubs
go
create table table1 (name varchar(100))
go
--drop table table2
create table table2 (name varchar(100), notes varchar(1000))
go
insert into table1 select 'Sarah'
insert into table1 select 'Michael'
insert into table1 select 'Paul'
go
insert into table2 select 'sarah','Smoker'
insert into table2 select 'sarah','Likes Bananas'
insert into table2 select 'sarah','Hates Fish'
insert into table2 select 'Michael','Likes Red'
insert into table2 select 'Michael','Enjoys Travel'
insert into table2 select 'Michael','Hate Blondes'
insert into table2 select 'Paul','Supports Leeds'
insert into table2 select 'Paul','Likes Coffee'
go
--drop function fn_Concat

Create FUNCTION fn_Concat (@col varchar(100))
returns varchar(1000)
as
begin
declare @x varchar(1000)
set @x=''
select @x=@x+':'+convert(varchar,notes) from table2 where ltrim(rtrim(name)) = ltrim(rtrim(@col))
set @x=substring(@x,2,len(@x))
return (@x)
end
go

select distinct a.name,dbo.fn_Concat (b.name) from table1 a, table2 b where a.name=b.name

[GrahamDuffield]

Thanks Mak.... I thing I'll try a different tak, as the limited sql editor didnt like the function...


!

[galileeian]

what database are you running

ian

[GrahamDuffield]

its an in-house application with built in SQL editor... I think its written in Delphi

[MAK]

What is the backend?

[GrahamDuffield]

have no idea ! just need to know now how to display the contents of a memo field with an SQL command !