Alternative to LISTAGG Function

Based on Oracle, LISTAGG orders data with each group specified in the ORDER BY clause and then concatenates the values of the measure column. It is a built in Function that enables us to perform string aggregation. This Function is easy to learn and understand with examples. To demo the purpose of LISTAGG Function, we will be using  emp table from baniya schema. This article is more on how do you LISTAGG string when the function LISTAGG is not available. 

LISTAGG Example Using Built in Function

SELECT deptno, 
       LISTAGG(ename, ',') 
         within GROUP( ORDER BY empno) AS "Last Names" 
FROM   baniya.emp 
GROUP  BY deptno 
ORDER  BY deptno;

Output:

Alternative to LISTAGG Function is to create a user Function our self and here is what the Function looks like.

CREATE OR REPLACE FUNCTION baniya.get_employee_names (p_deptno  IN  emp.deptno%TYPE)
  RETURN VARCHAR2
IS
  l_text  VARCHAR2(32767) := NULL;
  CURSOR cur_rec
  IS 
  SELECT ename FROM baniya.emp
  WHERE deptno = p_deptno;
  l_cur_rec  cur_rec%ROWTYPE;
BEGIN
  OPEN cur_rec;
  LOOP
    FETCH cur_rec INTO l_cur_rec;
    EXIT WHEN cur_rec%notfound;
    l_text := l_text || ',' || l_cur_rec.ename;
  END LOOP;
  RETURN LTRIM(l_text, ',');
  CLOSE cur_rec;
END;
/
SHOW ERRORS

Using the Function (ge_employee_names) in Select to generate LISTAGG result.

SELECT DISTINCT deptno, 
get_employee_names(deptno)
FROM baniya.emp;

Output:

SELECT 30,
 get_employee_names(30) 
FROM dual;

Output:

Sometime, it is nice to know how the built in function works and use it. It is fun to reverse engineer the built in Function and create your own. You can find an article on LISTAGG built in Function if you are not interested on the alternative.