Deduplicating and Creating Lists – Oracle SQL LISTAGG
By David Allan-Oracle on Aug 06, 2012
How do you leverage LISTAGG from ODI? How do you aggregate rows into strings or lists? Let’s see! Here we will see a few things including LISTAGG, an Oracle Analytic SQL function useful for creating lists from rows of information. We will also see how ODI can be extended to recognize this special function and generate the code we desire.
The data in our example has many order numbers for each customer. What we want is a single row for the customer and a comma separated list of order numbers for example. The LISTAGG function is perfect for this, and is blogged about all over the place. The example we will build in ODI takes the data from a source table and creates a comma separated list of order numbers;
To get ODI to recognize LISTAGG as an aggregation function we extend the ODI language elements in the Topology by adding a LISTAGG entry and define it as a ‘Group Function’ as below (I defined one implementation for Oracle and switched off the Universal flag). This will enable the ODI built-in aggregation analysis, so we get the group by generated automatically.
The ODI interface we will define looks like the following – we simply define the LISTAGG expression as an expression in the target column mapping and the CUSTID column will be the group by!
I did come across a limitation which would have been better had it not been there for user functions – they cannot include aggregation expressions, otherwise we could wrapper this up in a nice friendly user function (avoiding the technology specific grammar).
Other systems have such aggregation and list creation capabilities – for example if you look around Hypersonic SQL added GROUP_CONCAT for example to do similar list creation stuff, many others seem to be derived from the XMLAGG functions which build XML structures from relational.
The LISTAGG function is a useful function to remember.