Turning rows into a string using LISTAGG

by Granville Bonyata on May 25, 2012

With 11gR2, we get one of the cooler analytic functions yet – LISTAGG, which allows you to turn rows into a single string.

LISTAGG relies on the “GROUP BY” engine, so it’s efficient and easy to code:


SELECT name, listagg(phonenumber,', ') WITHIN GROUP (ORDER BY phonenumber) Numbers
FROM phone
GROUP BY name
/
Name        Numbers
Bob Smith   727-555-1001, 727-555-1002, 727-555-1003
Jay Lee     850-555-3001, 850-555-3001
Sam Jones   813-555-2001, 813-555-2001, 813-555-2003, 813-555-2004

  • PB

    This will be really useful!

Previous post:

Next post: