Return to Answer

SQL Fiddle

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

Results:

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

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

Results:

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

SQL Fiddle

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 | 

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 | 

CREATE OR REPLACE TYPE VARCHAR2s_Table AS TABLE OF VARCHAR2(4000); /   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; /   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; /   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 / 

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 / 

SQL Fiddle

CREATE OR REPLACE TYPE VARCHAR2s_Table AS TABLE OF VARCHAR2(4000); / 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; / 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; / 

SELECT concatStrings( CAST( COLLECT( str ) AS VARCHAR2s_Table )) AS concat_str FROM Strings 

SELECT concatStrings2( CAST( COLLECT( str ) AS VARCHAR2s_Table )) AS concat_str FROM Strings 

SQL Fiddle

CREATE OR REPLACE TYPE VARCHAR2s_Table AS TABLE OF VARCHAR2(4000); / 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; / 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; / 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 / 

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

Results:

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

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

Results:

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