Dave Webster's Blog

Tinkering with Oracle

Concatenating strings from multiple rows and an insight into XML in the DB

Posted by anotherdavewebster on August 30, 2010

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:
“CLU$,TAB$,IND$,COL$,UNDO$,USER$,TSQ$,FILE$,VIEWTRCOL$,TYPE_MISC$,SUBCOLTYPE$,REFCON$,OPQTYPE$,NTAB$,LOB$,LIBRARY$,ICOL$,COLTYPE$,ATTRCOL$”.

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.

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

 
%d bloggers like this: