Comma Separated Values

What are the different ways we can combine column values as Comma Separated Values in Oracle & SQL?

Oracle has introduced the aggregate function LISTAGG to get Comma separated values based on the Grouping Column.

Let’s say you have following Tables and you want Comma separated Employees List for each department as DEPTNO, DEPTNAME, EMPLOYEES

SQL> SELECT * From EMP_TEST;
 EMP_NO ENAME          DEPTNO HIREDATE
---------- ---------- ---------- ---------
      1001 Ravi               10 19-AUG-16
      1002 Surya              20 26-DEC-16
      1003 Ankit              30 12-DEC-16
      1004 Nikhil             40 12-DEC-10
      1005 Pritesh            50 19-AUG-16
      1006 Rajan              20 16-AUG-10
      1007 Manu               20 16-AUG-10
      1008 Karan              20 16-AUG-10
      1009 Gaurav             50 19-MAR-17
      1010 Shahrukh           40 11-MAR-17
      1011 Khan               30 11-MAR-16
11 rows selected.

SQL> SELECT * From DEPT_TEST;
    DEPTNO DEPTNAME
---------- --------------------
        10 Accounts
        20 Retail
        30 Insurance
        40 Banking
        50 Cloud

***Using LISTAGG:

SELECT E.DEPTNO, D.DEPTNAME,
LISTAGG(ENAME, ',') WITHIN GROUP (Order By ENAME) "EMPLOYEES"
From EMP_TEST E, DEPT_TEST D
Where E.DEPTNO=D.DEPTNO
Group By E.DEPTNO,D.DEPTNAME;

OUTPUT

***Using User Defined Function:

Before LISTAGG was introduced, we used to do this with User Defined Function. Let’s try that.

Purpose: All you have to do is to Create a Function which will Accept Department as an Input and will return List of Employees working for that Department.

Function Definition:

CREATE OR REPLACE FUNCTION GET_EMPLOYEES_LIST(IN_Deptno IN NUMBER)
RETURN VARCHAR2 IS
CURSOR C1 IS
SELECT Ename FROM EMP_TEST
WHERE Deptno = IN_Deptno
ORDER BY Ename;
l_ret VARCHAR2(2000);
BEGIN
FOR rec IN C1 
LOOP
  IF C1%ROWCOUNT > 1 THEN
         l_ret := l_ret || ', ';
  END IF;
  l_ret := l_ret || rec.ename;
END LOOP;
RETURN l_ret;
END;

SELECT Query:

SELECT E.DEPTNO, D.DEPTNAME,

GET_EMPLOYEES_LIST(E.DEPTNO) EMPLOYEES

From EMP_TEST E, DEPT_TEST D

Where E.DEPTNO=D.DEPTNO

Group By E.DEPTNO,D.DEPTNAME

ORDER BY E.DEPTNO;

OUTPUT

***Using REF Cursor:

In this case, we will first Define a REF Cursor which then will be passed to a Function to get the desired Output.

Type:

CREATE OR REPLACE PACKAGE MyPackage IS

TYPE Ref_Type IS REF CURSOR;
END MyPackage;

Function Definition:

CREATE OR REPLACE FUNCTION EMPLOYEES_LIST(In_refcur IN MyPackage.Ref_Type) 
RETURN VARCHAR2 IS
l_string VARCHAR2(2000);
l_ret VARCHAR2(2000);
BEGIN
LOOP
  FETCH in_refcur INTO l_string;
   EXIT WHEN in_refcur%NOTFOUND;
     IF in_refcur%ROWCOUNT > 1 THEN
         l_ret := l_ret || ', ';
     END IF;
     l_ret := l_ret || l_string;
END LOOP;
CLOSE in_refcur;
RETURN l_ret;
END;

SELECT Query:

SELECT D.DEPTNO, D.DEPTNAME,

EMPLOYEES_LIST(CURSOR(SELECT Ename FROM EMP_TEST E

WHERE E.Deptno = D.deptno ORDER BY Ename)) EMPLOYEES

FROM DEPT_TEST D

ORDER BY D.Deptno;

OUTPUT

***Using COLLECTIONS:

In this case, we will be creating a COLLECTION type and then passing that Type to a Function to get the desired output.

COLLECTION Type:

CREATE OR REPLACE TYPE MyType IS TABLE OF VARCHAR2(2000);

 Function Definition:

CREATE OR REPLACE FUNCTION GET_EMP_LIST(In_strings IN MyType) 
RETURN VARCHAR2 IS
l_ret VARCHAR2(2000);
BEGIN
 IF In_strings.COUNT > 0 THEN
    FOR i IN 1 .. in_strings.COUNT
    LOOP
      IF i > 1 THEN
        l_ret := l_ret || ', ';
      END IF;
      l_ret := l_ret || in_strings(i);
    END LOOP;
END IF;
RETURN l_ret;
END;

SELECT Query:

SELECT D.Deptno, D.Deptname,

GET_EMP_LIST(CAST(MULTISET(SELECT Ename FROM EMP_TEST E

                 WHERE E.Deptno = D.Deptno

                 ORDER BY Ename

             ) AS MyType

          )

       ) AS EMPLOYEES

FROM DEPT_TEST D

ORDER BY D.deptno;

OUTPUT

Get involved and leave your Comments in the Box Below. The more people get involved, the more we all benefit. So, leave your thoughts before you leave the page