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