This little trick came my way courtesy of a colleague of mine named Matt Cobley. Over the years, I’ve frequently needed to produce a comma delimited list of strings from multiple rows of a query. This is easy enough if you know how many rows you have but if not, I always end up writing a little piece of PL/SQL containing a cursor which builds a string which I then output to dbms_output. All rather long winded and more than a little limiting. That was until Matt sent an eMail (on a subject I no longer recall) containing the little gem XMLAGG which works something like this:
SELECT RTRIM( XMLAGG( XMLELEMENT( any_old_name, table_name || ',' ) ORDER BY last_analyzed DESC ).EXTRACT( '//text()' ) , ',' ) FROM dba_tables WHERE ROWNUM < 20 AND owner = 'SYS'
Now I don’t suppose for a minute that it’s cheap but it really is very easy and it provides us with a nice clean:
I made a mental note at the time to have a dig around the XML stuff in Oracle as I’m sure there are lots of other treats in there. I sorry to say that I’ve not yet got started.
UPDATE: A couple of you have kindly pointed out that 11r2 contains LISTAGG, an extension to the wonderful analytics functionality, which does the same job in a more elegant way.