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 -------------------------------------------------------------------------------- stu@myorg.com,mno@myorg.com,jkl@myorg.com,efg@myorg.com,lmn@myorg.com,xyz@myorg. com,pqr@myorg.com,abc@myorg.com,
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
--------------------------------------------------------------------------------
abc@myorg.com,pqr@myorg.com,xyz@myorg.com,lmn@myorg.com,efg@myorg.com,jkl@myorg.
com,mno@myorg.com,stu@myorg.com
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.
