Oracle LISTAGG: Concatenate Rows into One

1 year ago
William Carter
1 minute

You can use the LISTAGG function in Oracle to concatenate multiple rows of data into one row. The syntax of the LISTAGG function is as follows:

LISTAGG(expression, delimiter) WITHIN GROUP (ORDER BY order_by_clause)

In this case, expression refers to the column or expression to be concatenated, delimiter is the character used as a separator, and order_by_clause is the column or expression specifying the order of concatenation.

For example, let’s say there is a table called “employee” that contains the following data:

Employee ID
First Name
Last Name

1
John
Smith

2
Jane
Doe

To concatenate these data into one line, you can use the following query:

SELECT LISTAGG(first_name || ' ' || last_name, ', ') WITHIN GROUP (ORDER BY employee_id) AS full_name
FROM employee;

This will output: John Smith, Jane Doe.

bannerAds