String Aggregation In Oracle

To aggregate data from a number of rows into a single row where giving a list of data associated with a specific value.

For example if you want to select a list of employees for each department we have the following methods to achieve the result.
say EMP table of SCOTT schema contains the following data.

DEPTNO ENAME
---------- ----------
        20 SMITH
        30 ALLEN
        30 WARD
        20 JONES
        30 MARTIN
        30 BLAKE
        10 CLARK
        20 SCOTT
        10 KING
        30 TURNER
        20 ADAMS
        30 JAMES
        20 FORD
        10 MILLER

and we want to achieve the result as following:

DEPTNO EMPLOYEES
---------- ------------------------------------------------
        10 CLARK,KING,MILLER
        20 SMITH,FORD,ADAMS,SCOTT,JONES
        30 ALLEN,BLAKE,MARTIN,TURNER,JAMES,WARD
by using the following methods we can achieve the above result. 
this is called string aggregation. 

1. Using LISTAGG analytic function.
2. Using WM_CONCAT built-in function
3. By using COLLECT function
4. using ROW_NUMBER and SYS_CONNECT_BY_PATH functions.

LISTAGG:
i. it was introduced in oracle 11g release 2.
ii. by using this function we can aggregate strings very easily.
iii. it provides the advantage of ordering the elements in the concatenated list.

eg:

SELECT deptno, LISTAGG(ename, ',') WITHIN GROUP (ORDER BY ename) AS employees
FROM   emp
GROUP BY deptno;

    DEPTNO EMPLOYEES
---------- ------------------------------------------------
        10 CLARK,KING,MILLER
        20 ADAMS,FORD,JONES,SCOTT,SMITH
        30 ALLEN,BLAKE,JAMES,MARTIN,TURNER,WARD

WM_CONCAT:

SELECT deptno, wm_concat(ename) AS employees
FROM   emp
GROUP BY deptno;

    DEPTNO EMPLOYEES
---------- ------------------------------------------------
        10 CLARK,KING,MILLER
        20 SMITH,FORD,ADAMS,SCOTT,JONES
        30 ALLEN,BLAKE,MARTIN,TURNER,JAMES,WARD
COLLECT:
the collect function works from oracle 10g. in order to achieve the above same result by using COLLECT function we need a table type and a function to convert the contents of the table type to a string. 
follow the steps:
1. first create a table type
CREATE OR REPLACE  TYPE T_VAR2_TAB  AS TABLE OF VARCHAR2(4000);
/
2. create a function to convert the contents of the table type to string.
CREATE OR REPLACE FUNCTION tab_to_string (p_varchar2_tab  IN  t_var2_tab,
                                          p_delimiter     IN  VARCHAR2 DEFAULT ',') RETURN VARCHAR2 IS
  l_string     VARCHAR2(32767);
BEGIN
  FOR i IN p_varchar2_tab.FIRST .. p_varchar2_tab.LAST LOOP
    IF i != p_varchar2_tab.FIRST THEN
      l_string := l_string || p_delimiter;
    END IF;
    l_string := l_string || p_varchar2_tab(i);
  END LOOP;
  RETURN l_string;
END tab_to_string;
/
then query the function with the COLLECT function
SELECT deptno,
       tab_to_string(CAST(COLLECT(ename) AS t_var2_tab)) AS employees
FROM   emp
GROUP BY deptno;
       
    DEPTNO EMPLOYEES
---------- ------------------------------------------------
        10 CLARK,KING,MILLER
        20 SMITH,JONES,SCOTT,ADAMS,FORD
        30 ALLEN,WARD,MARTIN,BLAKE,TURNER,JAMES
        


4. ROW_NUMBER() and SYS_CONNECT_BY_PATH functions in Oracle 9i
the ROW_NUMBER() and SYS_CONNECT_BY_PATH functions to achieve the same result without the use of PL/SQL or additional type definitions.

SELECT deptno,
       LTRIM(MAX(SYS_CONNECT_BY_PATH(ename,','))
       KEEP (DENSE_RANK LAST ORDER BY curr),',') AS employees
FROM   (SELECT deptno,
               ename,
               ROW_NUMBER() OVER (PARTITION BY deptno ORDER BY ename) AS curr,
               ROW_NUMBER() OVER (PARTITION BY deptno ORDER BY ename) -1 AS prev
        FROM   emp)
GROUP BY deptno
CONNECT BY prev = PRIOR curr AND deptno = PRIOR deptno
START WITH curr = 1;

    DEPTNO EMPLOYEES
---------- ------------------------------------------------
       10 CLARK,KING,MILLER
       20 ADAMS,FORD,JONES,SCOTT,SMITH
       30 ALLEN,BLAKE,JAMES,MARTIN,TURNER,WARD