Deduplicating and Creating Lists – Oracle SQL LISTAGG

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;