Comma-Separated Values: How to Combine Column Values in Oracle and SQL (Basic 1 - 28)

Comma-separated values (CSVs) are a common way to store and exchange data. CSVs are easy to read and write, and they can be used by a variety of applications.

In Oracle and SQL, there are a number of ways to combine column values into CSVs. The best method to use will depend on your specific needs.

Using the LISTAGG function

The LISTAGG function is a built-in function in Oracle that can be used to aggregate a set of values into a comma-separated string. The function takes two arguments: the first argument is the expression to be aggregated, and the second argument is the delimiter to use between the values in the resulting string.

For example, the following query will return a comma-separated list of all employee names in the EMP_TEST table:

SQL

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

The output of this query will be a single string containing the names of all employees in the EMP_TEST table, separated by commas.

Using a user-defined function

Another way to combine column values into CSVs is to use a user-defined function. This can be useful if you need to customize the way that the CSV is generated.

For example, the following user-defined function can be used to generate a CSV of employee names, but it will also include the department name for each employee:

SQL

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 || ' (' || rec.deptno || ')';
END LOOP;
RETURN l_ret;
END;

To use this function, you would simply pass the department number to the function and it will return a comma-separated list of all employee names in that department, along with the department name.

For example, the following query will return a comma-separated list of all employee names in the EMP_TEST table, along with the department name for each employee:

SQL

SELECT get_employees_list(deptno) AS employees
FROM emp_test
ORDER BY deptno;

The output of this query will be a single string containing the names of all employees in the EMP_TEST table, along with the department name for each employee, separated by commas.

Other methods

There are a number of other ways to combine column values into CSVs, such as using a CASE statement, a REF cursor, or a COLLECTION type. However, the two methods described above are the most common methods.

Conclusion

This blog post has discussed the different ways to combine column values into CSVs in Oracle and SQL. The best method to use will depend on your specific needs. If you need to generate a simple CSV of a single column, then the LISTAGG function is a good option. If you need to customize the way that the CSV is generated, or if you need to combine values from multiple columns, then using a user-defined function is a better option.