Oracle SQL Query to Use LISTAGG for String Aggregation

Have you ever needed to combine multiple row values into a single concatenated string in Oracle SQL?

String aggregation is a common requirement in database reporting where you need to transform vertical data into horizontal comma-separated lists.

The LISTAGG function in Oracle SQL provides an elegant solution for concatenating values from multiple rows into a single delimited string.

This article explores the LISTAGG function's capabilities, syntax variations, practical examples, and best practices for effective string aggregation in Oracle databases.

What is LISTAGG in Oracle SQL?

LISTAGG is an Oracle SQL aggregate function that concatenates values from multiple rows into a single string with a specified delimiter.

This function operates similarly to other aggregate functions like SUM or COUNT but works specifically with string values.

Unlike traditional concatenation methods, LISTAGG intelligently handles grouping and ordering of concatenated values.

The function was introduced in Oracle Database 11g Release 2 and has become the standard method for string aggregation tasks.

How Does Oracle LISTAGG Syntax Work?

The basic LISTAGG syntax follows a straightforward pattern with optional parameters for customization.

LISTAGG(expression [, delimiter]) WITHIN GROUP (ORDER BY order_expression)

The expression parameter specifies the column or value to concatenate.

The delimiter parameter (optional) defines the separator between concatenated values, defaulting to NULL if omitted.

The WITHIN GROUP clause is mandatory and specifies the order of concatenated elements.

What are LISTAGG Parameters?

Each LISTAGG parameter serves a specific purpose in controlling the aggregation behavior:

• Expression: The column or calculated value to aggregate
• Delimiter: The string separator between values (commonly comma, semicolon, or pipe)
• ORDER BY: Determines the sequence of concatenated values
• DISTINCT: Removes duplicate values before concatenation (Oracle 19c+)
• ON OVERFLOW: Handles strings exceeding maximum length (Oracle 12c Release 2+)

When Should You Use LISTAGG?

LISTAGG excels in scenarios requiring row-to-column transformations for reporting and data presentation.

Common use cases include:

• Creating employee lists by department: Combining all employee names in each department into a single field
• Generating product catalogs: Aggregating product features or categories into comma-separated lists
• Building dynamic SQL statements: Concatenating column names or values for dynamic queries
• Report formatting: Converting normalized data into denormalized format for easier reading
• Data migration tasks: Preparing data for systems expecting delimited values

What are Practical Oracle LISTAGG Examples?

Let's explore comprehensive examples demonstrating LISTAGG capabilities with sample data.

Basic LISTAGG Example

First, we'll create sample tables and data:

-- Create employee table
CREATE TABLE exlst_employees (
    employee_id NUMBER PRIMARY KEY,
    employee_name VARCHAR2(50),
    department_id NUMBER,
    salary NUMBER,
    hire_date DATE
);

-- Insert sample data
INSERT INTO exlst_employees VALUES (1, 'John Smith', 10, 75000, DATE '2025-01-15');
INSERT INTO exlst_employees VALUES (2, 'Sarah Johnson', 10, 82000, DATE '2025-02-20');
INSERT INTO exlst_employees VALUES (3, 'Mike Wilson', 20, 68000, DATE '2025-01-10');
INSERT INTO exlst_employees VALUES (4, 'Emma Davis', 10, 79000, DATE '2025-03-05');
INSERT INTO exlst_employees VALUES (5, 'Robert Brown', 20, 71000, DATE '2025-02-15');
INSERT INTO exlst_employees VALUES (6, 'Lisa Anderson', 30, 85000, DATE '2025-01-25');
INSERT INTO exlst_employees VALUES (7, 'David Martinez', 30, 77000, DATE '2025-03-10');

-- Display the data
SELECT * FROM exlst_employees;

Query Result

Now let's use LISTAGG to aggregate employee names by department:

-- Aggregate employee names by department
SELECT 
    department_id,
    LISTAGG(employee_name, ', ') WITHIN GROUP (ORDER BY employee_name) AS employee_list
FROM exlst_employees
GROUP BY department_id
ORDER BY department_id;

Query Result

How to Use Oracle LISTAGG with Different Delimiters?

Different delimiters can enhance readability based on your requirements.

-- Using semicolon delimiter
SELECT 
    department_id,
    LISTAGG(employee_name, '; ') WITHIN GROUP (ORDER BY hire_date) AS employees_by_hire_date
FROM exlst_employees
GROUP BY department_id;

Query Result

Can LISTAGG Include Additional Information?

LISTAGG can concatenate complex expressions beyond simple column values.

-- Include salary information with employee names
SELECT 
    department_id,
    LISTAGG(employee_name || ' ($' || salary || ')', ', ') 
        WITHIN GROUP (ORDER BY salary DESC) AS employee_salary_list
FROM exlst_employees
GROUP BY department_id;

Query Result

How to Handle LISTAGG Overflow Errors?

LISTAGG results are limited to 4000 bytes in standard VARCHAR2 columns.

Oracle 12c Release 2 introduced the ON OVERFLOW clause to handle this limitation gracefully.

What is the ON OVERFLOW TRUNCATE Syntax?

-- Create a table with many records
CREATE TABLE exlst_products (
    product_id NUMBER,
    category VARCHAR2(20),
    product_name VARCHAR2(100)
);

-- Insert sample data
INSERT INTO exlst_products VALUES (1, 'Electronics', 'Laptop Computer Model XYZ-1000 with Extended Warranty');
INSERT INTO exlst_products VALUES (2, 'Electronics', 'Smartphone Latest Generation with Premium Features');
INSERT INTO exlst_products VALUES (3, 'Electronics', 'Tablet Device Professional Edition with Accessories');
-- Add more records as needed

-- Handle overflow with truncation
-- Handle overflow with truncation
SELECT 
    category,
    LISTAGG(product_name, ', ' ON OVERFLOW TRUNCATE '...') WITHIN GROUP (ORDER BY product_name)
        AS product_list
FROM exlst_products
GROUP BY category;

The ON OVERFLOW TRUNCATE clause prevents errors by truncating the result and optionally showing the count of omitted values.

What are LISTAGG Alternatives in Oracle?

Several alternatives exist for string aggregation, each with specific use cases.

WM_CONCAT Function (Deprecated)

WM_CONCAT was an undocumented function that provided similar functionality but is now deprecated.

-- Legacy approach (not recommended)
-- SELECT department_id, WM_CONCAT(employee_name) FROM exlst_employees GROUP BY department_id;

XMLAGG Function

XMLAGG provides an alternative for complex aggregations or when dealing with special characters.

-- Using XMLAGG for string aggregation
SELECT 
    department_id,
    RTRIM(XMLAGG(XMLELEMENT(e, employee_name || ',') ORDER BY employee_name).EXTRACT('//text()'), ',') AS employee_list
FROM exlst_employees
GROUP BY department_id;

Query Result

How Does LISTAGG Handle DISTINCT Values?

Oracle 19c introduced the DISTINCT keyword within LISTAGG to eliminate duplicates before aggregation.

-- Create a table with duplicate values
CREATE TABLE exlst_skills (
    employee_id NUMBER,
    skill_name VARCHAR2(50)
);

INSERT INTO exlst_skills VALUES (1, 'Java');
INSERT INTO exlst_skills VALUES (1, 'SQL');
INSERT INTO exlst_skills VALUES (1, 'Java'); -- Duplicate
INSERT INTO exlst_skills VALUES (2, 'Python');
INSERT INTO exlst_skills VALUES (2, 'SQL');
INSERT INTO exlst_skills VALUES (2, 'SQL'); -- Duplicate

-- Display the data
SELECT * FROM exlst_skills ORDER BY employee_id, skill_name;

Query Result

-- Using DISTINCT with LISTAGG (Oracle 19c+)
SELECT 
    employee_id,
    LISTAGG(DISTINCT skill_name, ', ') WITHIN GROUP (ORDER BY skill_name) AS unique_skills
FROM exlst_skills
GROUP BY employee_id;

Query Result

What are Performance Considerations for LISTAGG?

LISTAGG performance depends on several factors that database developers should consider.

Index Usage

Proper indexing on GROUP BY and ORDER BY columns significantly improves LISTAGG performance.

-- Create index for better performance
CREATE INDEX idx_exlst_emp_dept ON exlst_employees(department_id, employee_name);

Memory Consumption

LISTAGG operations consume memory proportional to the result string length and number of groups.

Large datasets with many unique values per group may require increased PGA memory allocation.

Alternative Approaches for Large Datasets

For extremely large concatenations exceeding 4000 bytes, consider these alternatives:

• CLOB aggregation: Use custom functions returning CLOB data types
• Pagination: Split results into manageable chunks
• Application-layer aggregation: Perform concatenation in the application layer
• Materialized views: Pre-aggregate frequently accessed data

How to Use LISTAGG in Complex Queries?

LISTAGG integrates seamlessly with other SQL features for advanced reporting needs.

Combining LISTAGG with Analytical Functions

-- Create sales data table
CREATE TABLE exlst_sales (
    sale_id NUMBER,
    product_category VARCHAR2(50),
    sale_month VARCHAR2(7),
    revenue NUMBER
);

INSERT INTO exlst_sales VALUES (1, 'Electronics', '2025-01', 15000);
INSERT INTO exlst_sales VALUES (2, 'Clothing', '2025-01', 8000);
INSERT INTO exlst_sales VALUES (3, 'Electronics', '2025-02', 18000);
INSERT INTO exlst_sales VALUES (4, 'Clothing', '2025-02', 9500);
INSERT INTO exlst_sales VALUES (5, 'Electronics', '2025-03', 20000);

-- Combine LISTAGG with window functions
SELECT DISTINCT
    product_category,
    SUM(revenue) OVER (PARTITION BY product_category) AS total_revenue,
    LISTAGG(sale_month || ':$' || revenue, ' | ') 
        WITHIN GROUP (ORDER BY sale_month) 
        OVER (PARTITION BY product_category) AS monthly_breakdown
FROM exlst_sales
ORDER BY product_category;

Query Result

Using LISTAGG in Subqueries

LISTAGG works effectively within subqueries for complex data transformations.

-- Nested LISTAGG example
SELECT 
    dept_summary.department_id,
    dept_summary.employee_count,
    dept_summary.employee_list,
    ROUND(dept_summary.avg_salary, 2) AS avg_salary
FROM (
    SELECT 
        department_id,
        COUNT(*) AS employee_count,
        LISTAGG(employee_name, ', ') WITHIN GROUP (ORDER BY employee_name) AS employee_list,
        AVG(salary) AS avg_salary
    FROM exlst_employees
    GROUP BY department_id
) dept_summary
WHERE dept_summary.employee_count > 1;

Query Result

Conclusion

The LISTAGG function represents a powerful tool in Oracle SQL for transforming row-based data into concatenated strings.

Its flexibility in handling delimiters, ordering, and overflow scenarios makes it indispensable for report generation and data transformation tasks.

Understanding LISTAGG syntax variations and performance implications enables developers to create efficient string aggregation queries.

While alternatives like XMLAGG exist, LISTAGG remains the preferred choice for most string aggregation requirements in modern Oracle databases.

By mastering LISTAGG capabilities and best practices, database professionals can significantly enhance their SQL query arsenal for complex reporting needs.