Friday, 18 August 2017
Technical

Combine multiple rows into a single string in Oracle

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],[email protected]
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],[email protected]
com,[email protected],[email protected]

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

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

 

Leave a Reply