Systems Engineering and RDBMS

listagg() – new analytic function in Oracle 11g R2

Posted by decipherinfosys on October 20, 2009

We all have used different methods in order to get a concatenated list of values from a column – pivoting it out, using SYS_CONNECT_BY_PATH function which was introduced in Oracle 10g R1, writing our own functions to do it etc..  In SQL Server also, using XML PATH, one can do it easily as illustrated in some of our posts before – one of them is here.

Oracle 11gR2 now has a new analytic function called listagg() for doing the list aggregation.  So, now all that we need to specify is the name of the column and the separator string that will separate the different values.  Here is the link to the 11gR2 documentation on this wonderful function along with some examples to help illustrate it’s usefulness:

http://download.oracle.com/docs/cd/E11882_01/server.112/e10592/functions087.htm

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: