How to substitute ids with names in a delimited string with a single SELECT statement

Suppose that you have a comma delimited line of department numbers and you need to get a comma delimited line of department names. Apparently, you could write a PL/SQL block, loop through the list of departments and concatenate them in a single text string. However, with a new powerful built-in function listagg that was introduced in Oracle 11gR2 you can accomplish the task in a single SELECT statement.

The idea is to split the ids first using approach I presented in a previous post and then use LISTAGG function to assemble the result character string.

var department_numbers VARCHAR2(200)

exec :department_numbers:='10,20,40'

col dept_names for a80

WITH x AS (
SELECT REGEXP_SUBSTR(:department_numbers, '[^,]+', 1, LEVEL) deptno
FROM dual
CONNECT BY LEVEL <= LENGTH(:department_numbers)-LENGTH(REPLACE(:department_numbers,','))+1
)
SELECT listagg (dname, ', ') WITHIN GROUP (ORDER BY dname) AS dept_names
FROM dept d JOIN x ON d.deptno=x.deptno;

Result:

DEPT_NAMES
----------------------------------
ACCOUNTING, OPERATIONS, RESEARCH

For more tricks and cool techniques check my book “Oracle SQL Tricks and Workarounds” for instructions.