Aggregate string connection in Oracle 10g [duplicate]

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.

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 

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 | 

 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