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: