Listagg

Working with Oracle SQL often means wrangling data into readable formats—especially when reporting or exporting data. One common challenge is concatenating values from multiple rows into a single string. Enter LISTAGG, Oracle’s purpose-built function for just that.

What is LISTAGG?

LISTAGG is an aggregate function introduced in Oracle 11g Release 2. It lets you combine values from multiple rows into a single, delimited string. Think of it as the SQL version of a JOIN operation, but for values in a single column.

Before LISTAGG, SQL developers relied on hacks using SYS_CONNECT_BY_PATH or XML-based approaches. Those methods worked, but they were messy, verbose, and error-prone. LISTAGG cleaned that up.

Syntax

Here’s the basic syntax:

LISTAGG(column_name, delimiter) WITHIN GROUP (ORDER BY column_name)

column_name: The column you want to aggregate.

delimiter: What you want to separate the values with (comma, space, slash, etc.).

WITHIN GROUP (ORDER BY …): Controls the order of concatenated values.

Example:

SELECT department_id,
       LISTAGG(employee_name, ', ') WITHIN GROUP (ORDER BY employee_name) AS employees
FROM employees
GROUP BY department_id;

This query gives you one row per department, listing all employees in that department, ordered alphabetically and separated by commas.

Why LISTAGG Matters

Concatenating values into a string can transform how data is presented. It’s especially useful in:

Reporting: Show all items purchased in one transaction.

Auditing: Combine all change reasons for a given record.

Debugging: Log multiple error messages tied to a single process or session.

LISTAGG vs GROUP BY

At a glance, LISTAGG looks like just another GROUP BY tool. But its real power is in shifting from vertical (row-based) to horizontal (string-based) aggregation. While SUM() or COUNT() reduce rows to a number, LISTAGG reduces them to readable strings.

Truncation Warning: The 4000-Character Limit

A key limitation of LISTAGG is that the output can’t exceed 4000 characters for VARCHAR2 (or 32767 in PL/SQL). If the result goes over the limit, Oracle throws an ORA-01489 error.

How to Handle It

Filter rows aggressively: Limit the result set before aggregation.

Truncate or abbreviate values: Use SUBSTR() or shorthand values.

Use LISTAGG with ON OVERFLOW TRUNCATE: This was introduced in Oracle 12c Release 2.

LISTAGG(column, ', ' ON OVERFLOW TRUNCATE WITH COUNT) 
WITHIN GROUP (ORDER BY column)

This will truncate the string and optionally add a count of how many values were omitted (e.g., “… (5 more)”).

Using DISTINCT with LISTAGG

Unfortunately, LISTAGG doesn’t support DISTINCT directly. But there’s a workaround:

SELECT department_id,
       LISTAGG(employee_name, ', ') WITHIN GROUP (ORDER BY employee_name)
FROM (
  SELECT DISTINCT department_id, employee_name
  FROM employees
)
GROUP BY department_id;

By de-duplicating the data in a subquery, you get a distinct list to feed into LISTAGG.

Real-World Use Case

Let’s say you run a music streaming service and want to list all genres a user has listened to:

SELECT user_id,
       LISTAGG(genre, ', ') WITHIN GROUP (ORDER BY genre) AS genres_listened
FROM user_listens
GROUP BY user_id;

Now instead of having 10 rows per user, each with a different genre, you have one neat, readable row per user.

Performance Considerations

While LISTAGG is powerful, it can become a bottleneck with large datasets or subqueries. Here’s how to keep it snappy:

Use appropriate indexes on the ORDER BY column.

Filter and reduce rows before aggregation.

Avoid LISTAGG in inner queries with lots of joins—materialize intermediate results first if needed.

Final Thoughts

LISTAGG is a surgical tool—best used when you need clean, readable string outputs from multi-row data. It brings SQL closer to how end-users think: not in rows and joins, but in readable lines and summaries.

Use it when:

You want human-friendly output.

You’re building dashboards, exports, or audit logs.

You need a quick string summary grouped by key fields.

Avoid it when:

The result could go over 4000 characters without truncation handling.

You’re doing high-volume OLTP operations—LISTAGG is not cheap.