Usage of listagg function in oracle

The LISTAGG function concatenates a set of rows of data into a string, using the specified concatenation character to separate elements. Usage includes: 1. Join all values ​​in a column; 2. Separate values ​​using join characters; 3. Ignore NULL values; 4. Partition by group; 5. Join values ​​sequentially.

Usage of LISTAGG function in Oracle

Definition:
The LISTAGG function will A set of rows of data are concatenated into a string, with the individual elements separated using the specified concatenation character.

Syntax:

<code>LISTAGG(expression, delimiter [IGNORE NULLS]) OVER (PARTITION BY partition_expression ORDER BY order_expression)</code>

Parameters:

• expression: the data column to be connected
• delimiter: Character delimiter between connection elements (optional)
• IGNORE NULLS: Ignore NULL values ​​(optional)
• partition_expression: Partitioned data column, specifying where the component elements are located Group of (optional)
• order_expression: Specifies the order in which elements are connected (optional)

Usage:

The LISTAGG function is usually used Used to combine multiple lines into a single string for easier display or processing. The following is its typical usage:

1. Concatenate all values ​​in a column

<code>SELECT LISTAGG(name) FROM table_name;</code>

2. Separate values ​​using joiners

<code>SELECT LISTAGG(name, ', ') FROM table_name;</code>

3. Ignore NULL values

<code>SELECT LISTAGG(name IGNORE NULLS) FROM table_name;</code>

4. Partition by group

<code>SELECT LISTAGG(name) OVER (PARTITION BY group_id) FROM table_name;</code>

5. Concatenate values ​​in order

<code>SELECT LISTAGG(name) OVER (ORDER BY name) FROM table_name;</code>

Example:

The following table shows an example of using the LISTAGG function to join employee names in the employees table:

Use the LISTAGG function to connect all employees Name:

<code>SELECT LISTAGG(name) FROM employees;</code>

Result:

<code>John, Jane, David</code>

Use comma as connector:

<code>SELECT LISTAGG(name, ', ') FROM employees;</code>

Result:

<code>John, Jane, David</code>

Partition by department and join the employees of each department Name:

<code>SELECT LISTAGG(name) OVER (PARTITION BY department) FROM employees;</code>

Result:

<code>John
Jane
David</code>

The above is the detailed content of Usage of listagg function in oracle. For more information, please follow other related articles on the PHP Chinese website!