Have you ever needed to list all locations under a common parent? If you use Oracle, this is a simple task. Oracle has a built-in function that allows you to string together multiple child records in a single row.
The Oracle listagg function is a built-in function that allows for many table columns to be displayed within a single row:
Example SQL:
select parent, listagg(location, ‘,’)
within group (order by location)
from lochierarchy
group by parent;
Example SQL Output:
Parent Location | Child Locations
Facility | Facility01, Facility02, Facility02, Facility03, Facility04
The function returns a concatenated list of all of the children of each parent. This is very useful when working with multiple location hierarchies in Maximo.
