[Oracle] LISTAGG function

Requirement
The following SQL statement will retrieve multiple row of data

SELECT PDATE,
       ACC || F_SYS_FIND_ACC_NM(:SCODE, :FYR, ACC, '000') AS ACC_NM
FROM CF0006
WHERE ACC LIKE '24%'
      AND LENGTH(TRIM(ACC))=11

But my requirement is to turn these multiple row of data into a single row per group.

Solution

Oracle provided LISTAGG function to fulfill this kind of requirement.

LISTAGG is an aggregate function that can optionally be used as an analytic (i.e. the optional OVER() clause). The following elements are mandatory:

• the column or expression to be aggregated;
• the WITHIN GROUP keywords;
• the ORDER BY clause within the grouping.

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.

For example. The following group-set aggregate example lists, for each pdate in the CF0006 table, the ACC_NM in that CF0006 in order of their pdate (separate with comma):

SELECT PDATE,
       LISTAGG(ACC || F_SYS_FIND_ACC_NM(:SCODE, :FYR, ACC, '000'), ', ') WITHIN
       GROUP (ORDER BY PDATE) "ACC_NM_LIST"
FROM CF0006
WHERE ACC LIKE '24%'
      AND LENGTH(TRIM(ACC))=11
GROUP BY PDATE;

Reference
[1] http://docs.oracle.com/cd/E11882_01/server.112/e10592/functions089.htm#SQLRF55593
[2] http://www.oracle-developer.net/display.php?id=515