Aggregate string connection in Oracle 10g

Question

I saw the previous question where the table had the columns "No" and "Name", and others that grouped with numeric columns, but was unable to implement the answers provided for my case. I need to do the same thing, but with non-numeric groupings. The source table is tbl1 with these columns:

POD    Name
---    -----
North  Rony
North  James
North  Aby
South  Sam
South  Willy
West   Mike

I need to do this aggregation:

POD    Name
---    -----
North  Aby,James,Rony
South  Sam,Willy
West   Mike

Since "POD" is non-numeric, the previous solutions by Msyma, Dinup, and chetan didn't seem to work for me.

I don't know how to make the knowledge transfer from their answers to these requirements.

An ideal query would be

SELECT POD, AGGREGATESTRING(Name)
FROM tbl1
GROUP BY POD

In the ideal example the AGGREGATESTRING doesn't sort the people's names, but I think I'll be able to insert an "ORDER BY" where needed.

Answer 1

Oracle 11g has this neat function LISTAGG that is pretty much what you want, however since you are on 10g this is not available to you (unless you decide to upgrade).

If for some reason you do not wish to (or can't due to whatever reasons) upgrade to 11g, I'd suggest looking at some alternatives to LISTAGG that are available to you on 10g.

You can check out some of the proposed alternatives here

Quickly adjusted a quick adaptation of one of the proposed alternatives to match your case scenario:

WITH Q AS
(
    SELECT 'North' POD, 'Rony' NAME FROM DUAL  UNION ALL
    SELECT 'North',     'James'     FROM DUAL  UNION ALL
    SELECT 'North',     'Aby'       FROM DUAL  UNION ALL
    SELECT 'South',     'Sam'       FROM DUAL  UNION ALL  
    SELECT 'South',     'Willy'     FROM DUAL  UNION ALL
    SELECT 'West',      'Mike'      FROM DUAL
)
SELECT   POD,
         RTRIM(
            XMLAGG (XMLELEMENT(e, name||',') ORDER BY name).EXTRACT('//text()'),
            ','
         ) AS name
    FROM q
GROUP BY POD;

But remember that this is not the actual solution as you'll have to tailor it according to your table (not the dummy DUAL table) etc...

Your solution will probably look something along the lines of:

SELECT   POD,
         RTRIM(
            XMLAGG (XMLELEMENT(E, NAME||',') ORDER BY NAME).EXTRACT('//text()'),
            ','
         ) AS NAME
    FROM tbl1
GROUP BY POD;

If you want to change the delimiter you can change it from comma in this part:

(E, NAME||',')

The RTRIM is there just to snip off the trailing comma from the end of the concatenated string, if you are not bothered by the trailing comma you can omit the RTRIM function to conserve readability.

Answer 2

yet one way WM_CONCAT

with Q as
 (select 'North' POD, 'Rony' name
    from DUAL
  union all
  select 'North', 'James'
    from DUAL
  union all
  select 'North', 'Aby'
    from DUAL
  union all
  select 'South', 'Sam'
    from DUAL
  union all
  select 'South', 'Willy'
    from DUAL
  union all
  select 'West', 'Mike' from DUAL)
select pod, to_char(wm_concat(name)) as name from q group by pod

string aggregation with hierarchical query

with Q as
 (select 'North' POD, 'Rony' name
    from DUAL
  union all
  select 'North', 'James'
    from DUAL
  union all
  select 'North', 'Aby'
    from DUAL
  union all
  select 'South', 'Sam'
    from DUAL
  union all
  select 'South', 'Willy'
    from DUAL
  union all
  select 'West', 'Mike' from DUAL)
select pod, group_name
  from (select t1.*, level as lv, substr(sys_connect_by_path(name, ','), 2) as group_name
          from (select t1.*, nvl(lag(row_rank) over(partition by pod order by row_rank), 0) as parent_row_rank
                  from (select q.*,
                               rank() over(partition by pod order by name) as row_rank,
                               rank() over(partition by pod order by name desc) as row_rank_desc
                          from q) t1) t1
         where row_rank_desc = 1
        connect by prior row_rank = parent_row_rank
               and prior pod = pod
         start with parent_row_rank = 0) t1

Answer 3

I have only been able to test this on Oracle 11g R2; however, I believe that everything is also available for Oracle 10g.

There are two functions included here both of which use collections:

• The first function is simpler;
• The second function makes use of the DBMS_LOB package and is more verbose but, on my tests, has seemed to be much much more efficient (although I would recommend profiling it yourself to test it).

SQL Fiddle

Oracle 11g R2 Schema Setup:

For this method, you will need to define a Collection to aggregate the strings into:

CREATE OR REPLACE TYPE VARCHAR2s_Table AS TABLE OF VARCHAR2(4000);
/

This function takes a string collection (and an optional delimiter) and returns a CLOB containing the concatenated strings - if you have a smaller dataset (as per your example) then this is probably overkill and you can replace CLOB with VARCHAR2.

CREATE OR REPLACE FUNCTION concatStrings(
  Strs VARCHAR2s_Table,
  delim VARCHAR2 DEFAULT ','
) RETURN CLOB
AS
  out_string CLOB;
BEGIN
  FOR i IN 1 .. Strs.COUNT LOOP
    out_string := out_string || CASE WHEN i = 1 THEN '' ELSE delim END || Strs(i);
  END LOOP;
  RETURN out_string;
END;
/

However, if you are going to have a long string returned as a CLOB then it may be more efficient to use some of the functions of the DBMS_LOB package:

CREATE OR REPLACE FUNCTION concatStrings2(
  Strs VARCHAR2s_Table,
  delim VARCHAR2 DEFAULT ','
) RETURN CLOB
AS
  out_string CLOB;
  dl         CONSTANT NUMBER(10) := LENGTH( delim );
BEGIN
  DBMS_LOB.CREATETEMPORARY( out_string, TRUE );
  IF strs IS NOT NULL AND strs IS NOT EMPTY THEN
    IF dl > 0 THEN
      DBMS_LOB.WRITEAPPEND( out_string, LENGTH( strs(1) ), strs(1) );
      FOR i IN 2 .. strs.COUNT LOOP
        DBMS_LOB.WRITEAPPEND( out_string, dl, delim );
        DBMS_LOB.WRITEAPPEND( out_string, LENGTH( strs(i) ), strs(i) );
      END LOOP;
    ELSE
      FOR i IN 1 .. strs.COUNT LOOP
        DBMS_LOB.WRITEAPPEND( out_string, LENGTH( strs(i) ), strs(i) );
      END LOOP;
    END IF;
  END IF;
  RETURN out_string;
END concatStrings2;
/

Your test data:

CREATE TABLE tbl1 ( POD, name ) AS
          SELECT 'North', 'Rony' FROM DUAL
UNION ALL SELECT 'North', 'James' FROM DUAL
UNION ALL SELECT 'North', 'Aby' FROM DUAL
UNION ALL SELECT 'South', 'Sam' FROM DUAL
UNION ALL SELECT 'South', 'Willy' FROM DUAL
UNION ALL SELECT 'West', 'Mike' FROM DUAL
/

Query 1:

SELECT POD,
       concatStrings( CAST( COLLECT( name ORDER BY name ASC ) AS VARCHAR2s_Table )) AS name
FROM   tbl1
GROUP BY POD

Results:

|   POD |           NAME |
|-------|----------------|
| North | Aby,James,Rony |
| South |      Sam,Willy |
|  West |           Mike |

Query 2:

SELECT POD,
       concatStrings2( CAST( COLLECT( name ORDER BY name ASC ) AS VARCHAR2s_Table )) AS name
FROM   tbl1
GROUP BY POD

Results:

|   POD |           NAME |
|-------|----------------|
| North | Aby,James,Rony |
| South |      Sam,Willy |
|  West |           Mike |

Answer 4

    SELECT POD,WM_CONCAT(NAME) AS AGG_STRING FROM TEST_AV
    GROUP BY POD;

----------------------------------------------------------------------------------

POD     AGG_STRING 
NORTH   JANES,RONY,ABY 
SOUTH   WILLY 
WEST    MIKE 

3 rows returned in 0.07 seconds