Concatenating Repeating Row Data in a Single Column 1

[adventurous1]

I need to be able to put all of the repeats into just one row of a table.

Using a similar example, my source data looks like this:
Field1 Field2 Field3
Joe Smith 555-555-5555 CogOrder1
Joe Smith 555-555-5555 CogOrder2
Joe Smith 555-555-5555 CogOrder3

Needed Output:

Field1 Field2 Field3
Joe Smith 555-555-5555 CogOrder1,CogOrder2,CogOrder3

etc....

Can this be done in SQL or does it require a PL/SQL program?

Can anyone help me with the coding for this?

Any help offered is appreciated.

Adventurous1@ureach.com

 

[SantaMufasa]

Adventurous,

My solution for your need involves a user-defined function (PL/SQL), but the invocation is SQL. Following are three sections: 1) Sample data, 2) Function definition, 3) SQL invocation and results.

Section 1 -- Sample data:

SQL> select * from adventurous;

FIELD1                         FIELD2                         FIELD3
------------------------------ ------------------------------ ---------
Joe Smith                      555-555-5555                   CogOrder1
Joe Smith                      555-555-5555                   CogOrder2
Joe Smith                      555-555-5555                   CogOrder3
Hyrum Smith                    555-555-4444                   CogOrder6
Hyrum Smith                    555-555-4444                   CogOrder7
Hyrum Smith                    555-555-4444                   CogOrder8
Hyrum Smith                    555-555-4444                   CogOrder9

7 rows selected.

Section 2 -- Function definition:

create or replace function advent (f1 in varchar2, f2 in varchar2) return varchar2 is
	hold_string	varchar2(4000);
begin
	for r in (select field3 from adventurous where f1 = field1 and f2 = field2) loop
		if length(hold_string) > 0 then
			hold_string := hold_string||', ';
		end if;
		hold_string := hold_string||r.field3;
	end loop;
	return hold_string;
end;
/

Section 3 -- SQL invocation and results:

col a heading "FIELD1" format a11
col b heading "FIELD2" format a12
col c heading "FIELD3" format a42 word_wrapv
select field1, field2, advent(field1,field2)a
from adventurous
group by field1, field2
/
FIELD1      FIELD2       FIELD3
----------- ------------ ------------------------------------------
Joe Smith   555-555-5555 CogOrder1, CogOrder2, CogOrder3
Hyrum Smith 555-555-4444 CogOrder6, CogOrder7, CogOrder8, CogOrder9

2 rows selected.

Let me know if this works for you.

Mufasa
(aka Dave of Sandy, Utah, USA @ 17:50 (06Mar04) UTC (aka "GMT" and "Zulu"), 10:50 (06Mar04) Mountain Time)

 

[adventurous1]

Mufasa,

Thank you soooo much for helping with this! I will be testing this shortly and will update the post with the results.

Thanks,

Adventurous