How to Use LISTAGG in Oracle
Last updated August 25, 2026 · By the SaturnSQL team
LISTAGG(expr, delim) WITHIN GROUP (ORDER BY) joins rows into a string. Wide groups exceed 4000 bytes and raise ORA-01489; ON OVERFLOW TRUNCATE fixes it.
SELECT department_id,
LISTAGG(last_name, ', ') WITHIN GROUP (ORDER BY last_name) AS team
FROM employees
GROUP BY department_id;DISTINCT and partitioned aggregates
LISTAGG(DISTINCT expr, delimiter) drops duplicate values before concatenating (available since Oracle 19c). Adding OVER (PARTITION BY ...) turns LISTAGG into an analytic function that repeats the aggregated list on every row of the partition instead of collapsing to one row per group.
SELECT order_id, customer_id,
LISTAGG(product_name, ', ') WITHIN GROUP (ORDER BY product_name)
OVER (PARTITION BY order_id) AS all_products
FROM order_items;The 4000-byte overflow
LISTAGG raises ORA-01489: result of string concatenation is too long once the aggregated string exceeds the VARCHAR2 limit (4000 bytes by default, or 32767 if MAX_STRING_SIZE is set to EXTENDED). Wide groups with many rows hit this in production even though a small test table never triggers it. ON OVERFLOW TRUNCATE (12.2+) caps the output and appends a truncation indicator instead of erroring, and WITH COUNT appends how many values were dropped.
SELECT department_id,
LISTAGG(last_name, ', ' ON OVERFLOW TRUNCATE '...' WITH COUNT)
WITHIN GROUP (ORDER BY last_name) AS team
FROM employees
GROUP BY department_id;Run this in SaturnSQL
SaturnSQL is a browser-based SQL editor for teams: shared query library, schema-aware autocomplete, and scheduled exports to Google Sheets and Slack.
Try it free