Skip to content
Home / Database / Combine multiple rows into a single string in Oracle

Combine multiple rows into a single string in Oracle

Oracle Listagg function helps to concatenate multiple rows into a single string. This article explains this in details.

Once a developer asked, “How to combine multiple rows into a single string delimited by comma, or pipe?” The requirement is to get email id from a table, concatenate those by “,” and use it as a parameter while sending an email.

“This is how it is, is there any better way?”

“Can we remove that for loop?”

Example: PL/QL function to group rows and return concatenated single string

SET SERVEROUTPUT ON 
CREATE OR REPLACE FUNCTION get_email
   RETURN VARCHAR2
IS
   l_mail VARCHAR2(1000) := NULL;
BEGIN
   FOR i IN
   (SELECT email
      FROM demo_user    
   )
   LOOP
      l_mail := i.email ||','||l_mail;
   END LOOP;
dbms_output.put_line('Email ' || l_mail);
RETURN l_mail;
EXCEPTION
WHEN OTHERS THEN
   dbms_output.put_line('Exception in mail function :'||SQLCODE||SQLERRM);
   RETURN '-1';
END;
SELECT get_email email FROM dual;

EMAIL
--------------------------------------------------------------------------------
[email protected],[email protected],[email protected],[email protected],[email protected],xyz@myorg.
com,[email protected],[email protected],

Googled it and found a built-in Oracle function LISTAGG. This is available in Oracle Database 11G Release 2.

This made the job very easy. Entire PL/SQL logic is replaced with single SQL.

Example: – Listagg Oracle built-in function to return group of rows as single string

SELECT listagg(email,',')
     WITHIN GROUP (ORDER BY user_id)
 FROM demo_user;

EMAIL
--------------------------------------------------------------------------------
[email protected],[email protected],[email protected],[email protected],[email protected],jkl@myorg.
com,[email protected],[email protected]

Listagg operates on a group of rows and returns as a single column with user-defined concatenate character.

In the above example, I used a comma (,). You can use a comma, semi-colon or any valid character.

Summary

The listagg function in PL/SQL provides an easy way to Combine multiple rows into a single string.