I have a table containing names & addresses.  Sometimes, the same
address may appear more than once, each with a different name.  When
this happens, I need my result set to contain the address record one
time with the names concatenated in the name field.  For example:

JOHN SMITH      108 MAIN ST      LITTLE ROCK, AR
JANE SMITH      108 MAIN ST      LITTLE ROCK, AR

Result set is to be:

JOHN SMITH & JANE SMITH     108 MAIN ST    LITTLE ROCK, AR

I have no trouble finding the records (using count/having).  But is it
possible for me to create these "combined" records using SQL?  Any code
suggestions appreciated.

Thanks, in advance...

David

David,

<< I have a table containing names & addresses.  Sometimes, the same address
may appear more than once, each with a different name.  When
this happens, I need my result set to contain the address record one time
with the names concatenated in the name field.  For example:

JOHN SMITH      108 MAIN ST      LITTLE ROCK, AR
JANE SMITH      108 MAIN ST      LITTLE ROCK, AR

Result set is to be:

JOHN SMITH & JANE SMITH     108 MAIN ST    LITTLE ROCK, AR

I have no trouble finding the records (using count/having).  But is it
possible for me to create these "combined" records using SQL?  Any code
suggestions appreciated. >>

You can do the grouping by simply using a GROUP BY clause on the necessary
fields.  The concatenation, however, cannot be done "in-process" with the
GROUPING.  This basically means that you're going to have to do both outside
of the context of an SQL statement in navigational code.

--
Tim Young
Elevate Software
www.elevatesoft.com

"Tim Young [Elevate Software]" <timyoung@elevatesoft.com> wrote in message
news:5D68A4D4-D67F-4DA6-BF08-86C2CC6946F2@news.elevatesoft.com...
> David,
>
> << I have a table containing names & addresses.  Sometimes, the same
> address may appear more than once, each with a different name.  When
> this happens, I need my result set to contain the address record one time
> with the names concatenated in the name field.  For example:
>
> JOHN SMITH      108 MAIN ST      LITTLE ROCK, AR
> JANE SMITH      108 MAIN ST      LITTLE ROCK, AR
>
> Result set is to be:
>
> JOHN SMITH & JANE SMITH     108 MAIN ST    LITTLE ROCK, AR
>
> I have no trouble finding the records (using count/having).  But is it
> possible for me to create these "combined" records using SQL?  Any code
> suggestions appreciated. >>
>
> You can do the grouping by simply using a GROUP BY clause on the necessary
> fields.  The concatenation, however, cannot be done "in-process" with the
> GROUPING.  This basically means that you're going to have to do both
> outside of the context of an SQL statement in navigational code.
>

Try this, no guarantees

select name, address into memory\temp1 from nameaddress;
alter table memory\temp1 add id autoinc, add baserec boolean default
'false';
select count(*) as dups, address into memory\temp2 from nameaddress group by
address having dups > 1;
select  min(id) thebase, address into memory\temp3 from memory\temp1 group
by address;
update memory\temp1 t1 set t1.baserec = true
from memory\temp1 t1
join memory\temp3 t2 on t2.thebase = t1.id;
update memory\temp1 t1 set name = t1.name + ' & ' + t2.name
from memory\temp1 t1
join memory\temp1 t2 on (t1.address = t2.address)  and (t1.id <> t2.id)
join memory\temp2 t3 on t3.address = t1.address
where t1.baserec = true;
delete from memory\temp1 where baserec = false;
select * from memory\temp1;

Robert

Thanks for these replies.  I'll definitely tinker with trying that code
-- it is an interesting approach.

If it works I'll ask you where to send the check ..

Thanks guys...

"David Ray" <david@timecalc.com> wrote in message
news:5022082A-0618-4B0A-9E6E-15009E39DE29@news.elevatesoft.com...
> Thanks for these replies.  I'll definitely tinker with trying that code --  
> it is an interesting approach.
>
> If it works I'll ask you where to send the check ..
>

I created a small table containing just name and address and, to my
amazement, it worked. Of course, with a large table you'd have to build the
appropriate indexes on the memory tables or be prepared to wait a looong
time for the result.

Robert