When you want to work on larger datasets, the processing time will be higher as the query needs to check all the rows and columns in a database. To reduce the processing time, you can concatenate column values from multiple rows using Oracle. This makes the query more efficient and easier to analyze the data. This also helps us to understand the data and makes fetching the data easier. In this blog, you will explore in detail the different methods to concatenate column values from multiple rows in Oracle.
Table of Contents:
Methods to Concatenate Column Values from Multiple Rows in Oracle
Some methods can be used to concatenate the column values from multiple rows, like LISTAGG(), XMLAGG(), and COLLECT() with CAST in Oracle Database.
Let’s create a table to perform the operations on how to concatenate column values from multiple rows in Oracle.
-- Create the table
CREATE TABLE hotel_dishes (
hotel_id NUMBER,
hotel_name VARCHAR2(100),
dish_name VARCHAR2(100)
);
-- Insert sample data
INSERT INTO hotel_dishes VALUES (1, 'Grand Palace', 'Butter Chicken');
INSERT INTO hotel_dishes VALUES (1, 'Grand Palace', 'Paneer Tikka');
INSERT INTO hotel_dishes VALUES (1, 'Grand Palace', 'Mutton Biryani');
INSERT INTO hotel_dishes VALUES (2, 'Ocean View', 'Grilled Salmon');
INSERT INTO hotel_dishes VALUES (2, 'Ocean View', 'Lobster Thermidor');
INSERT INTO hotel_dishes VALUES (3, 'Mountain Retreat', 'Stuffed Mushrooms');
INSERT INTO hotel_dishes VALUES (3, 'Mountain Retreat', 'Veg Lasagna');
-- Commit the transaction
COMMIT;
COLUMN hotel_id FORMAT 9999
COLUMN hotel_name FORMAT A30
COLUMN best_dishes FORMAT A100
SET LINESIZE 200
SET PAGESIZE 50
SELECT * FROM hotel_dishes;
Output:
The table will look like this after creating and inserting the values.
Method 1: Using LISTAGG() in Oracle
The LISTAGG() in Oracle is very efficient when we need to convert column values from multiple rows. The LISTAGG() works well with larger datasets.
Syntax:
SELECT column1,
LISTAGG(column2, ', ') WITHIN GROUP (ORDER BY column2) AS concatenated_values
FROM table_name
GROUP BY column1;
Example:
-- Run the main query with proper formatting
SELECT hotel_id,
RPAD(hotel_name, 30) AS hotel_name,
LISTAGG(dish_name, ', ') WITHIN GROUP (ORDER BY dish_name) AS best_dishes
FROM hotel_dishes
GROUP BY hotel_id, hotel_name
ORDER BY hotel_id;
Output:
Explanation: Here, the LISTAGG(dish_name, ‘, ‘) command converts the data into strings with the help of a delimiter that separates the strings. Then the GROUP BY function groups the strings based on category.
Method 2: Using XMLAGG() in Oracle
The XMLAGG() function in Oracle works similarly to LISTAGG() but is primarily used in older Oracle versions. It can effectively handle large tables.
Syntax for XMLAGG() in Oracle:
SELECT column1,
RTRIM(XMLAGG(XMLELEMENT(E, column2 || ', ')).EXTRACT('//text()'), ', ') AS concatenated_value
FROM table_name
GROUP BY column1;
Example:
SELECT hotel_name,
RTRIM(XMLAGG(XMLELEMENT(E, dish_name || ', ')).EXTRACT('//text()'), ', ') AS best_dishes
FROM hotel_dishes
GROUP BY hotel_name;
Output:
Explanation: Here, the XMLAGG() separates the strings with separators and considers them as elements, and the GROUP BY then groups all the elements based on their category (hotel name).
Method 3: Using COLLECT() in Oracle
The COLLECT() in Oracle is not suitable when you want your output in a readable format. But it can provide you with the output with the DBMS_DEBUG_VC2COLL tag. It is useful when you want your data as an aggregated function in PL/SQL for advanced processing.
Syntax for COLLECT()
SELECT column1,
CAST(COLLECT(column2) AS collection_type) AS aggregated_values
FROM table_name
GROUP BY column1;
Example:
SELECT hotel_name,
CAST(COLLECT(dish_name) AS SYS.DBMS_DEBUG_VC2COLL) AS best_dishes
FROM hotel_dishes
GROUP BY hotel_name;
Output:
Explanation: Here, the COLLECT() function processed the output in a string format with a tag of DBMS_DEBUG_VC2COLL. This is stored in a PL/SQL collection format, which is useful for further processing in PL/SQL procedures.
Method 4: JSON_ARRAYAGG() in Oracle
The JSON_ARRAYAGG() in Oracle is an aggregate function that gives output as a JSON array with the values from multiple rows. It combines values from different rows and converts them into a single JSON array string.
Syntax:
JSON_ARRAYAGG(expression [ RETURNING data_type ] [ FORMAT JSON ])
Example:
SELECT JSON_ARRAYAGG(hotel_name) AS names_list
FROM hotel_dishes;
Output:
Explanation: Here, the JSON_ARRAYAGG() function collects all the values from multiple rows and returns them as a single JSON array string.
Alternative method using CURSOR and STRING AGGREGATION in PL/SQL
It is a very efficient method, as it does not have any character limits. It uses a cursor, which concatenates the rows manually. Works best in PL/SQL server
Example:
-- Create the table
CREATE TABLE hotel_dishes (
hotel_id NUMBER,
hotel_name VARCHAR2(100),
dish_name VARCHAR2(100)
);
-- Insert sample data
INSERT INTO hotel_dishes VALUES (1, 'Grand Palace', 'Butter Chicken');
INSERT INTO hotel_dishes VALUES (1, 'Grand Palace', 'Paneer Tikka');
INSERT INTO hotel_dishes VALUES (1, 'Grand Palace', 'Mutton Biryani');
INSERT INTO hotel_dishes VALUES (2, 'Ocean View', 'Grilled Salmon');
INSERT INTO hotel_dishes VALUES (2, 'Ocean View', 'Lobster Thermidor');
INSERT INTO hotel_dishes VALUES (3, 'Mountain Retreat', 'Stuffed Mushrooms');
INSERT INTO hotel_dishes VALUES (3, 'Mountain Retreat', 'Veg Lasagna');
-- Commit the transaction
COMMIT;
COLUMN hotel_id FORMAT 9999
COLUMN hotel_name FORMAT A30
COLUMN best_dishes FORMAT A100
SET LINESIZE 200
SET PAGESIZE 50
DECLARE
v_result VARCHAR2(4000);
BEGIN
FOR rec IN (SELECT dish_name FROM hotel_dishes WHERE hotel_name = 'Grand Palace') LOOP
v_result := v_result || rec.dish_name || ', ';
END LOOP;
-- Remove the last comma and space
v_result := RTRIM(v_result, ', ');
DBMS_OUTPUT.PUT_LINE(v_result);
END;
/
Output:
Explanation: Here, the WHERE clause fetches only the data from the hotel named “Grand Palace” with the help of the SELECT query.
|
Method |
Performance |
Limitations |
Best Use Case |
| LISTAGG() | You can use this method for small to medium datasets. Very efficient when used for ordered datasets. | Cannot handle a long list as it accepts only 4000 characters. | It is very useful for making reports. |
| XMLAGG() | It can handle huge tables better than LISTAGG(). | Overhead is involved in XML handling. Later versions have removed this. | It is useful when you use older versions of Oracle. |
| COLLECT() with CAST | It can work better in PL/SQL and can be used in an advanced database. | The output is not readable. It needs formatting to make the output readable. | Best in PL/SQL, where the application needed further processing. |
Use Cases
- It combines columns into many rows. That makes the data readable and arranged. It reduces query redundancy. It can be achieved by LISTAGG() to make the data structured and XMLAGG() to use in larger datasets.
- It enhances query performance, especially for larger tables. It reduces the number of rows as it combines multiple rows together. This can be very helpful when you use XMLAGG().
- It transfers the data efficiently as it sends multiple rows of data into a single comma-separated string. It reduces the memory space while transferring. XML transfers the file in XML format, which makes it easier.
- It uses the PL/SQL logic to store the data efficiently, which reduces the memory space. COLLECT() with CAST() is best, as it performs bulk operations.
Real-World Examples
Example 1: Concatenating Employee Skills in an HR Database
CREATE TABLE employee_skills (
employee_id NUMBER,
employee_name VARCHAR2(100),
skill VARCHAR2(100)
);
INSERT INTO employee_skills VALUES (101, 'Alice', 'Java');
INSERT INTO employee_skills VALUES (101, 'Alice', 'Python');
INSERT INTO employee_skills VALUES (101, 'Alice', 'SQL');
INSERT INTO employee_skills VALUES (102, 'Bob', 'C++');
INSERT INTO employee_skills VALUES (102, 'Bob', 'JavaScript');
INSERT INTO employee_skills VALUES (103, 'Charlie', 'Python');
INSERT INTO employee_skills VALUES (103, 'Charlie', 'R');
COMMIT;
COLUMN employee_id FORMAT 9999
COLUMN employee_name FORMAT A20
COLUMN skills FORMAT A50
SET LINESIZE 100
SET PAGESIZE 50
SELECT employee_id,
RPAD(employee_name, 20) AS employee_name,
LISTAGG(skill, ', ') WITHIN GROUP (ORDER BY skill) AS skills
FROM employee_skills
GROUP BY employee_id, employee_name
ORDER BY employee_id;
Output:
Explanation: Here, the LISTAGG() gets the skills in a comma-separated string, then GROUP BY groups them based on the employee details from the HR database.
Example 2: displaying products based on categories.
CREATE TABLE product_categories (
product_id NUMBER,
product_name VARCHAR2(100),
category VARCHAR2(100)
);
INSERT INTO product_categories VALUES (1, 'Laptop', 'Electronics');
INSERT INTO product_categories VALUES (1, 'Laptop', 'Computers');
INSERT INTO product_categories VALUES (2, 'Smartphone', 'Electronics');
INSERT INTO product_categories VALUES (2, 'Smartphone', 'Mobiles');
INSERT INTO product_categories VALUES (3, 'Sofa', 'Furniture');
INSERT INTO product_categories VALUES (3, 'Sofa', 'Home Decor');
COMMIT;
COLUMN product_id FORMAT 9999
COLUMN product_name FORMAT A20
COLUMN categories FORMAT A50
SET LINESIZE 100
SET PAGESIZE 50
SELECT product_id,
RPAD(product_name, 20) AS product_name,
RTRIM(XMLAGG(XMLELEMENT(E, category || ', ')).EXTRACT('//text()'), ', ') AS categories
FROM product_categories
GROUP BY product_id, product_name
ORDER BY product_id;
Output:
Explanation: Here, the XMLAGG() fetches the categories of products and segregates them based on the product name.
Conclusion
You can concatenate column values from multiple rows in Oracle by using methods like LISTAGG(), XMLAGG(), and COLLECT(), and also alternative methods for selective concatenation like CURSOR and STRING AGGREGATION. These methods are very effective and improve the quality of data in a table, and also reduce the memory space by converting the large datasets into comma-separated strings, XML format, and also as PL/SQL objects. You can use any of these methods based on your dataset specifications.
Take your skills to the next level by enrolling in our SQL Training Course today and gain hands-on experience. Also, prepare for job interviews with our SQL interview questions, prepared by industry experts.
How to Concatenate Column Values from Multiple Rows in Oracle – FAQs
1. How to concatenate multiple rows in Oracle SQL?
Use LISTAGG() or XMLAGG() to concatenate multiple row values into a single string.
2. How do I concatenate multiple rows in one column?
Use LISTAGG(column_name, ‘, ‘) WITHIN GROUP (ORDER BY column_name) to merge values into one column.
3. How to convert multiple rows into columns in Oracle?
Use the PIVOT function to transform multiple rows into columns.
4. How do you combine values from multiple rows into a single row in Power Query?
Use the Group By function and select All Rows, then use Text.Combine([ColumnName], “, “) to merge values.
5. How do you split values into multiple rows?
Use CONNECT BY LEVEL with the REGEXP_SUBSTR() or TABLE() function for splitting comma-separated values into rows.