Oracle LISTAGG()

오라클 LISTAGG()

 

LISTAGG()

   - 

 

LISTAGG(measure_expr, 'delimiter') WITHIN GROUP(order_by_clause),

LISTAGG(measure_expr, 'delimiter') WITHIN GROUP(order_by_clause) OVER query_partition_clause

의 형태

 

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 archieved unique ordering.

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

 

오라클 oe계정 또는 hr 계정으로 테스트가 가능하다.

 

Aggregate Example

 

SQL> 

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

 

--> Raphaely;Khoo;Tobias;Baida;Himuro;Colmenares         2002/12/07

 

SQL>

SELECT
    department_id "Dept",
    LISTAGG(last_name, ';') WITHIN GROUP(ORDER BY hire_date) "Employees"
FROM employees
GROUP BY department_id
ORDER BY department_id;    

 

 

Analytic Example

SQL>

SELECT
    department_id "Dept",
    hire_date "Date",
    last_name "Name",
    LISTAGG(last_name, ';') WITHIN GROUP (ORDER BY hire_date, last_name) OVER (PARTITION BY department_id) as "Emp_list"
FROM employees
WHERE hire_date < '01-SEP-2003'
ORDER BY "Dept", "Date", "Name";