New feature in 11gR2 - listagg

Although this is more straight SQL than PL/SQL it is something I dicovered recently and found interesting. With Release 2 of the 11g Oracle database they have added, amoungst other things, a new built in function to created a concatenated list of values from a query. The new function is listagg. So you can create output of the form.


Dept Code Employees ID's
10  213, 214, 216, 218
20  220, 230, 298
30  450, 451, 850, 021, 823


Which you need to do occasionally. I have seen a requirement like this to create a comma separated list of email addresses to create an email from a table.
For example if you wanted code to create a mailing list from the employee ID's above based on department so that you could an email per department.


Dept  Employees email
10  max@company.com, bob@company.com, jen@company.com
20    cat@company.com, tom@company.com


The way this could be done was to loop through a cursor and while the value of department code was the same value as the one for the previous loop keep adding to the string of addresses. When the department value changed then complete the previous string and start the next string for the new department code. Which is easy to do with PL/SQL.

To do this with listagg it would look like this


SELECT emp.dept_id,
listagg(emp.email_addr, ', ') -- the column used in the
concatenated string and the delimiter
  within GROUP
  (ORDER BY emp.emp_id asc)  -- the order of values within the
concatenated string
FROM employee emp
WHERE emp.email_addr IS NOT NULL
GROUP BY emp.dept_id
ORDER BY emp.dept_id



If you omit the delimiter argument

listagg(emp.email_addr)

the default is a straight concatenation with no delimiter at all like this


mary@company.combob@company.comalice@company.commike@company.com

So if you want to have a space instead you need to say


listagg(emp.email_addr, ' ')

which would give you


mary@company.com bob@company.com alice@company.com mike@company.com

Of course there are limitations to how big this resultant string can be. In this case it is the VARCHAR2(4000) limit. If you exceed 4000 characters you will get the error


ORA-01489: result of string concatenation is too long

To get around this error you need to take a different approach. This isn't as convenient as using listagg and will result in a CLOB but it will work with longer data. Below is an example using xmlagg where it concatenates all employee last_name records for each department_id and orders within the concatenated records by the last_name.


SELECT emp.department_id,
rtrim(
         xmlserialize(content
           extract(
             xmlagg(xmlelement("big_string", 
                               emp.last_name||',') 
                               ORDER BY emp.last_name)
           , '//text()'
           )
         )
       , ','
       ) as list
FROM employees emp
GROUP BY emp.department_id

I've also got another post using xmlagg at multiple levels if that is needed. The same approach could be applied here if needed.

If the approach above does not do what you need you can also use an example that was shown on the Ask Tom site.

http://asktom.oracle.com/pls/asktom/f?p=100:11:0::NO::P11_QUESTION_ID:2196162600402

If you know another solution then leave a comment.