How to concatenate values from multiple rows into a single string with a specified delimiter ( comma seperated ) LIST_AGG

The LIST_AGG function in Oracle is used to concatenate values from multiple rows into a single string with a specified delimiter. It is commonly used for grouped string aggregation.

---

Basic Syntax:

LIST_AGG(column_name, 'delimiter') WITHIN GROUP (ORDER BY column_name)

• column_name → The column whose values you want to concatenate.
• 'delimiter' → The separator between values (e.g., ', ' or ' | ').
• ORDER BY → Defines the order in which values appear in the concatenated result.

---

Example 1: Concatenating Names

Let's say we have a EMPLOYEES table:

DEPT_ID	EMP_NAME
10	Alice
10	Bob
20	Charlie
20	David
20	Eve

We want to group employees by DEPT_ID and list names separated by commas.

SELECT DEPT_ID, LIST_AGG(EMP_NAME, ', ') WITHIN GROUP (ORDER BY EMP_NAME) AS EMPLOYEES
FROM EMPLOYEES
GROUP BY DEPT_ID;

Output:

DEPT_ID	EMPLOYEES
10	Alice, Bob
20	Charlie, David, Eve

---

Example 2: Concatenating Order IDs for Each Customer

SELECT CUSTOMER_ID, LIST_AGG(ORDER_ID, ', ') WITHIN GROUP (ORDER BY ORDER_DATE DESC) AS ORDERS
FROM ORDERS
GROUP BY CUSTOMER_ID;

This will list all ORDER_IDs for each customer in descending order of date.

---

Example 3: Using LIST_AGG with DISTINCT (Oracle 19c+)

From Oracle 19c onwards, you can use DISTINCT inside LIST_AGG:

SELECT DEPT_ID, LIST_AGG(DISTINCT EMP_NAME, ', ') WITHIN GROUP (ORDER BY EMP_NAME) AS EMPLOYEES
FROM EMPLOYEES
GROUP BY DEPT_ID;

👉 This ensures duplicate names are not repeated.

---

Handling Overflow: LISTAGG with ON OVERFLOW (Oracle 12.2+)

If the concatenated string exceeds 4000 characters (CLOB limit in Oracle 12.2), it will cause an error.
Oracle 12.2+ allows handling it gracefully using ON OVERFLOW:

SELECT LIST_AGG(EMP_NAME, ', ') WITHIN GROUP (ORDER BY EMP_NAME) 
       ON OVERFLOW TRUNCATE '...' 
FROM EMPLOYEES;

👉 This truncates the output and appends "..." instead of throwing an error.

---

Alternative: Using XMLAGG for Older Versions

For Oracle before 11g, you can use XMLAGG instead of LIST_AGG:

SELECT DEPT_ID,
       RTRIM(XMLAGG(XMLELEMENT(E, EMP_NAME || ', ')).EXTRACT('//text()'), ', ') AS EMPLOYEES
FROM EMPLOYEES
GROUP BY DEPT_ID;

👉 This achieves the same effect but works in Oracle 10g and older.

---

Summary

• ✅ LIST_AGG(column, 'delimiter') WITHIN GROUP (ORDER BY column) concatenates values.
• ✅ DISTINCT in LIST_AGG is available from Oracle 19c+.
• ✅ ON OVERFLOW TRUNCATE prevents errors in long concatenations (Oracle 12.2+).
• ✅ For older Oracle versions, use XMLAGG instead.