concatenate different rows into one field

[stroy]

I am looking for a way to concatenate data from different rows.

TableA
----------
A Albert
B Bob
C Celine

TableB
----------
A 555-5555
A 555-6666
B 555-7777


I want
--------
Albert 555-5555, 555-6666
Bob 555-7777
Celine

Is there a query that would do this, a standard function or know user function to do this?
>select col2, myConcat(select col2 from tableB where TableA.col1 = TableB.col1) as xxx from TableA


There may be an infinite number of rows
Thank you
Stephan

 

[swampBoogie]

There is no standard function for that so you have to write your own if you wish to do it with SQL.

In most cases it easier to do this in the application, i.e. retrieve the data sorted by name and keep track of when the name changes.

 

[stroy]

I can't do this in my code because my query will be sorted on another field.

 

[swampBoogie]

Ok which DBMS are you using?

 

[stroy]

oracle 8

 

[carp]

SELECT a.name||' '||b.phone
FROM tablea a, tableb b
WHERE a.id = b.id(+);


I haven't tested this, but I think it should work for you.

 

[stroy]

Thank you carp but I no but want to concatenate the name and one phone, but all the phone numbers together.

column 1 => the name
column 2 => all the phone numbers concatenated

Stroy

 

[swampBoogie]

You need a UDF with a cursor loop. I'm not using Oracle but I can write it using PSM (ANSI SQL definition for stored procedure) and you should be able to convert it to Oracle.

create function assimilate(pID varchar(20))
returns vachar(2000)) reads sql data
begin
declare a cursor for select phone from b where id = pID;
declare s varchar(2000) default '';
declare p varchar(20);
open a;
begin
declare exit handler for not found begin end;
loop
fetch a into p;
set s = s || p;
end loop;
end;
close a;
return s;
end

and the you could use that function as

select assimilate(id) , name from A
end

 

[carp]

You can't do this in SQL. You'll probably wind up using PL/SQL and cursor loops (as noted by swampBoogie):

DECLARE
lv_number VARCHAR2(1000);
CURSOR phone_cursor(p_id VARCHAR2) IS
SELECT phone FROM tableB
WHERE id = p_id;
BEGIN
FOR i IN (SELECT id, name FROM tableA) LOOP
lv_number := NULL;
FOR j IN phone_cursor(i.id) LOOP
IF (phone_cursor%ROWCOUNT = 1) THEN
lv_number := j.phone;
ELSE
lv_number := lv_number||', '||j.phone;
END IF;
END LOOP;
INSERT INTO tableC VALUES(i.id, i.name, lv_number);
COMMIT;
END LOOP;
END;

Again, untested but I think it should be pretty close.