Aggregation of multiple values from same column as a concatenated string (10g)

Question

I have a very long query, across multiple tables, where I have concatenated four values as owners (first, middle and last name + org). All other columns are the same, but there are multiple owners, thus, multiple rows which I would like to aggregate.

What I am seeing is (paired down)

#     Owner
1     Sam Smith, AAA
2     Stan Bird, BBB
2     Nancy Bird, BBB
3     Mike Owen, CCC

What I would like to see is

#     Owner
1     Sam Smith, AAA
2     Stan Bird, Nancy Bird, BBB
3     Mike Owen, CCC

Caveats:

• I can not create functions (lack of privileges)
• Using Oracle 10g

• I have attempted CASE(COLLECT... but this kills my connection:

  Error- "No more data to read from socket"

  SysAdmin, isn't sure why

• WM_CONCAT simply repeats one owner (sometimes 20 times) and is not giving the desired result.

I have tried a few other things, with no luck. My current query is producing the desired number of rows, but is just lopping off the second owner.

I am unsure if it would be wise to post the entire query here. Please let me know if this would be helpful.

---

Update 2012-01-29

I was using wm_concat incorrectly before, but when I use it as you have shown, I am getting this error:

 ORA-06502: PL/SQL: numeric or value error: character string buffer too
 small ORA-06512: at "WMSYS.WM_CONCAT_IMPL", line 30
 06502. 00000 -  "PL/SQL: numeric or value error%s"
 *Cause:    
 *Action:

I believe there may be some higher level issues at play. My sysadmin had no answer for the "No more data to read from socket" issue. Could this be another.

My SQL knowledge is limited, and with the length and complexity of the query, I can't seem to implement the sys_connect_by_path; completely my own fault.

Sorry about the delayed response. I was pulled away to complete another task. Thank you for all of your help. Thank you ShadowWizard for the bounty.

EDIT
Here is how I have used wm_concat in the current instance:

 replace(cast(wm_concat(PERSON.MASTER_PERSON_FIRST_NAME   || ' '   ||
 PERSON.MASTER_PERSON_MIDDLE_INITIAL   || ' '   ||
 PERSON.MASTER_PERSON_LAST_NAME   || ','   || ' '   ||
 ORGANIZATION.MASTER_ORG_NAME) AS VARCHAR2(1000 BYTE)), ',', ', ') AS
 "Owner(s)",

Sorry, forgot to include that.

Answer 1

Not sure why wm_concat didn't work for you, but I suspect you had it at the wrong level or were grouping oddly.

If I set up some dummy data:

create table issues (id number);

create table owners (id number, first varchar2(10), middle varchar2(10),
    last varchar2(10), org varchar2(3));

create table issue_owners (issue_id number, owner_id number);

insert into issues (id) values (1);
insert into issues (id) values (2);
insert into issues (id) values (3);

insert into owners (id, first, middle, last, org)
    values (11, 'Sam', null, 'Smith', 'AAA');
insert into owners (id, first, middle, last, org)
    values (12, 'Stan', null, 'Bird', 'BBB');
insert into owners (id, first, middle, last, org)
    values (13, 'Nancy', null, 'Bird', 'BBB');
insert into owners (id, first, middle, last, org)
    values (14, 'Mike', null, 'Owen', 'CCC');

insert into issue_owners (issue_id, owner_id) values (1, 11);
insert into issue_owners (issue_id, owner_id) values (2, 12);
insert into issue_owners (issue_id, owner_id) values (2, 13);
insert into issue_owners (issue_id, owner_id) values (3, 14);

... which gives the same initial output as your paired-down sample:

column issue_id format 9 heading "#"
column owner format a50 heading "Owner"

select i.id as issue_id,
    o.first
        || case when o.middle is null then null else ' ' || o.middle end
        || ' ' || last || ', ' ||o.org as owner
from issues i
left join issue_owners io on io.issue_id = i.id
left join owners o on o.id = io.owner_id
order by issue_id, owner;

 # Owner
-- --------------------------------------------------
 1 Sam Smith, AAA
 2 Nancy Bird, BBB
 2 Stan Bird, BBB
 3 Mike Owen, CCC

4 rows selected.

I can use wm_concat to aggregate the names:

select issue_id,
    replace(cast(wm_concat(owner_name) as varchar2(4000)), ',', ', ')
        || ', ' || owner_org as owner
from (
    select i.id as issue_id,
        o.first
            || case when o.middle is null then null else ' ' || o.middle end
            || ' ' || last as owner_name,
        o.org as owner_org
    from issues i
    left join issue_owners io on io.issue_id = i.id
    left join owners o on o.id = io.owner_id
)
group by issue_id, owner_org
order by issue_id, owner;

 # Owner
-- --------------------------------------------------
 1 Sam Smith, AAA
 2 Stan Bird, Nancy Bird, BBB
 3 Mike Owen, CCC

3 rows selected.

The replace is just putting the space between the names, which isn't entirely relevant, and I'm casting to varchar2 because wm_concat returns a clob which causes a problem concatenating the org. At least, it's a clob in 11gR2 - I don't have a 10g instance with wm_concat available, but I think it returns varchar2 in earlier versions; if so the cast isn't needed and it would be more like:

select issue_id,
    replace(wm_concat(owner_name), ',', ', ') || ', ' || owner_org as owner
from (
...

I'm not sure where your org value is coming from so this is probably simplified, and I don't know what you want to happen if the org is linked to a person (rather than an issue, or your equivalent) and an issue has two owners with different org values.

---

If this isn't getting you any closer then maybe you can post a cut-down version of your query, replacing the long multiple-table part with some fixed data and showing how you're trying to use wm_concat against it; or your own version of sample data-build that shows the same behaviour.

---

Alternative sys_connect_by_path method as suggested Appleman1234, for the same data:

select issue_id,
    ltrim(max(sys_connect_by_path(owner_name, ', '))
        keep (dense_rank last order by curr), ', ')
        || ', ' || owner_org as owner
from (
    select issue_id,
        owner_name,
        owner_org,
        row_number() over (partition by issue_id order by owner_name) as curr,
        row_number() over (partition by issue_id order by owner_name) - 1 as prev
    from (
        select i.id as issue_id,
            o.first
                || case when o.middle is null then null else ' ' || o.middle end
                || ' ' || last as owner_name,
            o.org as owner_org
        from issues i
        left join issue_owners io on io.issue_id = i.id
        left join owners o on o.id = io.owner_id
    )
)
group by issue_id, owner_org
connect by prev = prior curr and issue_id = PRIOR issue_id
start with curr = 1;

 # Owner
-- --------------------------------------------------
 1 Sam Smith, AAA
 2 Nancy Bird, Stan Bird, BBB
 3 Mike Owen, CCC

3 rows selected.

If you end up using that, Appleman1234 should add an answer and I'll remove this part, as he should get credit for suggesting it! I wanted to try it anyway, I've seen it before but hadn't remembered it...