The attached function provides a way to put together a list of multivalued fields for a row, delimited by a character, such as | or ,. 

You most likely already have the DELIMLIST function in your database, but if you don't, you can run the attached file while logged into a third-party tool, used to connect to Oracle (i.e. SQL Plus, SQL Developer, Tora, or Toad), as the biotics_dlink user.  Make sure to Grant/Refresh Database Privileges prior to trying to use the function.

To run the following queries in Query Builder, make sure not to include a ; at the end of the query, which is necessary when running the query in SQL Plus. Including the trailing ; in Query Builder will result in an error indicating Invalid Character. 

Examples:

The following query is using the delimlist function to return distinct (via distinct) Counties, Mapsheets, and Managed Areas for a given EO (via ||eo.eo_id) with the values within each of those fields separated by a comma (via ', '):

SELECT EO.shape_id,
  EO.EO_ID,
  delimlist('SELECT distinct(d_county.county_name) FROM d_county,eo_county,eo WHERE d_county.D_COUNTY_ID = eo_county.D_COUNTY_ID and eo_county.eo_id ='||eo.eo_id,', ') "County",
  delimlist('SELECT distinct(d_mapsheet.mapsheet_name) FROM d_mapsheet,eo_mapsheet,eo WHERE d_mapsheet.D_MAPSHEET_ID = eo_MAPSHEET.D_MAPSHEET_ID and eo_mapsheet.eo_id ='||eo.eo_id,', ') "Mapsheet",
  delimlist('SELECT distinct(ma.managed_area_name) FROM ma, eo_managed_area, eo WHERE ma.MANAGED_AREA_ID = eo_managed_area.MANAGED_AREA_ID and eo_managed_area.EO_ID ='||eo.eo_id,', ') "Managed Area"
FROM EO;

SELECT
delimlist(
'SELECT d_terrestrial_habitat_id'
|| ' FROM animal_cag_terr_hab'
|| ' WHERE element_global_id = ' || element_global_id) AS terrestrial_habitat
FROM element_global WHERE element_global_id = 17442

In the following example, if you need single quotes somewhere inside your select statement (as in the DLOOKUP function), you have to type the single quote two times so it gets interpreted properly by DELIMLIST.

SELECT
elcode_bcd,
delimlist(
'SELECT DLOOKUP(''D_TERRESTRIAL_HABITAT'', d_terrestrial_habitat_id)'
|| ' FROM animal_cag_terr_hab'
|| ' WHERE element_global_id = ' || element_global_id) AS terrestrial_habitat
FROM element_global WHERE element_global_id = 17442