How to Use LISTAGG Function to Concatenate Strings in PL/SQL?

In PL/SQL, concatenating strings from multiple rows into a single value is a common requirement. The LISTAGG function offers a convenient solution for this task, allowing users to aggregate strings and generate a delimited list.

The LISTAGG function simplifies complex SQL queries, enhancing efficiency in data manipulation and presentation within PL/SQL environments. In this article, we will learn how to use the LISTAGG Function to concatenate strings in PL/SQL.

Using the LISTAGG Function

The LISTAGG function collects data from multiple rows into a single string value, separated by a specified delimiter. It simplifies the process of concatenating strings in PL/SQL queries, which makes it easier to work with grouped data.

Syntax of LISTAGG function in PL/SQL:

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

Here, column_name refers to the column containing the strings to be concatenated, while delimiter denotes the separator between concatenated elements. Additionally, the ORDER BY clause arranges the concatenated strings in a specified order.

Setting Up the Environment

For demonstrating Examples, First, we will create a table and then insert some values. After insertion, we will see and understand how to use the LISTAGG Function to concatenate strings in PL/SQL.

Query:

CREATE TABLE orders (
    order_id INT,
    soldby VARCHAR(50),
    product_name VARCHAR(100)
);
INSERT INTO orders (order_id, soldby, product_name) VALUES (1, 'John', 'Laptop');
INSERT INTO orders (order_id, soldby, product_name) VALUES (1, 'John', 'Mouse');
INSERT INTO orders (order_id, soldby, product_name) VALUES (1, 'John', 'Keyboard');
INSERT INTO orders (order_id, soldby, product_name) VALUES (2, 'Alice', 'Smartphone');
INSERT INTO orders (order_id, soldby, product_name) VALUES (2, 'Alice', 'Headphones');
INSERT INTO orders (order_id, soldby, product_name) VALUES (3, 'Emily', 'Tablet');
INSERT INTO orders (order_id, soldby, product_name) VALUES (3, 'Emily', 'Charger');

SELECT * FROM ORDERS;

Output:

orders table

Note: If the table is not showing properly in terminal then run these queries first.

set linesize 200;
set pagesize 200;

Now let’s see the below examples to understand the concept.

Examples with Explanation on How to use LISTAGG Function

Example 1: Concatenating Products for Each Order ID

In this example, we are retrieving data from the orders table. We want to concatenate the product_name column for each distinct order_id.

SELECT order_id, LISTAGG(product_name, ', ') WITHIN GROUP (ORDER BY product_name) AS concatenated_products
FROM orders
GROUP BY order_id;

Output:

ORDER_ID	CONCATENATED_PRODUCTS
1	Keyboard, Laptop, Mouse
2	Headphones, Smartphone
3	Charger, Tablet

Explanation:

• The LISTAGG function concatenates the product names into a single string, separated by commas, and orders them alphabetically.
• We use the GROUP BY clause to group the results by order_id.
• The result shows the concatenated product names for each order ID.

Example 2: Concatenating Products Sold by Each Salesperson

Here, we want to see the products sold by each salesperson. We use the LISTAGG function to concatenate the product_name column for each distinct soldby value.

Query:

SELECT soldby, LISTAGG(product_name, ', ') WITHIN GROUP (ORDER BY product_name) AS products_sold
FROM orders
GROUP BY soldby;

Output:

SOLDBY	PRODUCTS_SOLD
Alice	Headphones, Smartphone
Emily	Charger, Tablet
John	Keyboard, Laptop, Mouse

Explanation:

• Products are ordered alphabetically within each concatenated string.
• The GROUP BY clause groups the results by the soldby column.
• The output displays the salesperson’s name along with the products they sold, separated by commas.

Example 3: Concatenating Salespersons for Each Product

In this example, we are trying to show the salespersons associated with each product. We use the LISTAGG function to concatenate the soldby column for each distinct product_name.

Query:

SELECT product_name, LISTAGG(soldby, ', ') WITHIN GROUP (ORDER BY soldby) AS salespersons
FROM orders
GROUP BY product_name;

Output:

PRODUCT_NAME	SALESPERSONS
Charger	Emily
Headphones	Alice
Keyboard	John
Laptop	John
Mouse	John
Smartphone	Alice
Tablet	Emily

Explanation:

• Salespersons names are ordered alphabetically within each concatenated string.
• The GROUP BY clause groups the results by the product_name column.
• The output presents each product along with the salespersons who sold it, separated by commas.

Benefits of LISTAGG

• Simplicity: LISTAGG simplifies string concatenation, eliminating the need for complex SQL queries or iterative procedures.
• Efficiency: By streamlining the concatenation process, LISTAGG enhances query performance and reduces resource consumption.
• Readability: Concatenated strings offer enhanced readability, enabling users to glean insights from consolidated data effortlessly.

Conclusion

The LISTAGG function in PL/SQL helps developers to make the string concatenation process smooth, efficient, and improves data presentation and manipulation. By using LISTAGG, users can enhance their database management practices, facilitating efficient analysis, interpretation, and representation of data.