Using Related Field as a Prompt Field without using Translate values

Before learning the process of having related field as a prompt field, you need to know the final output of PIA screen looks like, check out the below screen.

here actually the Profession field store only one character like "E", "H", but user can experience it values in their screens. Do the following steps to do the task titled.

1. Create the Record with field you actually want to store in DB. in our example it is MV_PROF

2. Assign the Prompt Table which you want to prompt while clicking the Related Field prompt. As shown above

3. Make sure that your Prompt Table has the Field you are displaying (Descr) is having Alternative Search Key and List Box Item checked

4. Comes to page design, have the Display Control Field properties like below,

5. And Related Field properties like below,

Selecting multiple rows into a single row - Oracle 11g feature

Like you I was also searching this solution, finally we have a compact function introduced in Oracle 11g

Function   :    listagg()
Syntax      :    

For a specified measure, LISTAGG orders data within each group specified in the ORDER BY clause and then concatenates the values of the measure column.

• As a single-set aggregate function, LISTAGG operates on all rows and returns a single output row.
• As a group-set aggregate, the function operates on and returns an output row for each group defined by the GROUP BY clause.
• As an analytic function, LISTAGG partitions the query result set into groups based on one or more expression in the query_partition_clause.

The arguments to the function are subject to the following rules:

• The measure_expr can be any expression. Null values in the measure column are ignored.
• The delimiter_expr designates the string that is to separate the measure values. This clause is optional and defaults to NULL.
• The order_by_clause determines the order in which the concatenated values are returned. The function is deterministic only if the ORDER BY column list achieved unique ordering.

The return data type is RAW if the measure column is RAW; otherwise the return value is VARCHAR2.

SELECT LISTAGG(last_name, '; ')
         WITHIN GROUP (ORDER BY hire_date, last_name) "Emp_list",
       MIN(hire_date) "Earliest"
  FROM employees
  WHERE department_id = 30;

Emp_list                                                     Earliest
------------------------------------------------------------ ---------
Raphaely; Khoo; Tobias; Baida; Himuro; Colmenares            07-DEC-02

in some advanced cased you may use the alternatives of listadd() function,
like XMLAgg(), SYS_XMLAgg()

for more details follow the below references,

Oracle Documentation - xmlagg()