In database management, it is often necessary to merge data from multiple rows into a single string list for better data display and analysis. GBase database (GBase数据库) offers an aggregate function, LISTAGG, which not only combines data but also allows sorting and custom delimiters. This article provides an in-depth introduction to the LISTAGG function, including its syntax, features, use cases, and considerations in practical applications.

Overview of the LISTAGG Function

The primary purpose of the LISTAGG aggregate function is to combine multiple rows of data into a single string list. This process includes both data merging and sorting to ensure logical ordering. Additionally, with a specified delimiter, we can define the separation format of the items in the list, accommodating various data display requirements.

Syntax and Use Cases of LISTAGG

Syntax

The syntax for the LISTAGG function is as follows:

LISTAGG(measure_expr [, 'delimiter']) WITHIN GROUP (ORDER BY order_by_clause)

• measure_expr: The expression for the rows to be combined. This supports column names, constants, and expressions but cannot be omitted.
• delimiter: The separator string for rows, supporting one or multiple characters. If omitted, no delimiter is used.
• order_by_clause: The expression within the WITHIN GROUP clause determines the sort order of the data before aggregation. This step is essential for LISTAGG to merge strings in the specified sequence. Multiple column names are supported, and the sorting follows the order of columns specified from left to right. This cannot be omitted.

Additional Notes:

• The function returns a character-type value. If the length of the aggregated string exceeds the maximum allowed length in GBase8s (32765), an error will be raised.
• The resulting string from LISTAGG does not include NULL values.

Use Case Analysis

To better understand how to use the LISTAGG function, let's look at some practical examples.

Note: The examples below use GBase8s version GBase8sV8.8_TL_3.5.1_x86_64.

For demonstration, we will use a simple employees table with some sample data.

CREATE TABLE employees (
   employee_id int,
   department_id VARCHAR2(20),
   employee_name VARCHAR2(20)
);

-- Insert 6 rows of data:
insert into employees values (101, 'depart1', 'carry');
insert into employees values (102, 'depart1', 'sam');
insert into employees values (201, 'depart2', 'joice');
insert into employees values (202, 'depart2', 'tim');
insert into employees values (203, 'depart2', 'Amy');
insert into employees values (301, 'depart3', 'rose');

> select * from employees order by employee_id;
employee_id department_id        employee_name
       101 depart1              carry
       102 depart1              sam
       201 depart2              joice
       202 depart2              tim
       203 depart2              Amy
       301 depart3              rose
6 row(s) retrieved.

Use Case 1: Without a GROUP BY clause (single group aggregation), data rows are treated as one large group. Here, data is merged based on employee_id in ascending order by default.

> select listagg( employee_name, ',') within group(order by employee_id ) fname  from employees;

fname carry,sam,joice,tim,Amy,rose

1 row(s) retrieved.

Use Case 2: With a GROUP BY clause, the LISTAGG function provides all users within each department, sorted by employee_id.

> select department_id, listagg( employee_name, ',') within group(order by employee_id) fname  from employees group by department_id order by department_id;

department_id  depart1
fname          carry,sam

department_id  depart2
fname          joice,tim,Amy

department_id  depart3
fname          rose

3 row(s) retrieved.

Note: Like other aggregate functions, all projected columns must be included in the GROUP BY clause; otherwise, an error will occur.

> select department_id, listagg( employee_name, ',') within group(order by employee_id) fname  from employees order by department_id;

294: The column (department_id) must be in the GROUP BY list.
Error in line 1
Near character position 109

Use Case 3: When there is a NULL value in the data, it is ignored and does not appear in the final string list.

insert into employees(employee_id,department_id) values (302,'depart3');

>  select * from employees where department_id='depart3';

employee_id department_id        employee_name
       301 depart3                rose
       302 depart3
2 row(s) retrieved.

> select department_id, listagg( employee_name, ',') within group(order by employee_id) fname  from employees group by department_id order by department_id;

department_id  depart1
fname          carry,sam
department_id  depart2
fname          joice,tim,Amy
department_id  depart3
fname          rose            -- NULL is ignored

3 row(s) retrieved.

Use Case 4: Using an expression for measure_expr.

> select listagg(case when employee_id <200 then 100 when employee_id>200 and employee_id <300 then 200 else 300 end, '|') within group(order by employee_id) fname  from employees group by department_id;

FNAME  100|100
FNAME  300|300
FNAME  200|200|200
3 row(s) retrieved.

Use Case 5: The order_by_clause expression supports multiple columns.

> select department_id, listagg( employee_name, ',') within group(order by department_id,employee_name) fname  from employees group by department_id ;

DEPARTMENT_ID  depart1
FNAME          carry,sa
DEPARTMENT_ID  depart3
FNAME          rose
DEPARTMENT_ID  depart2
FNAME          Amy,joice,tim
3 row(s) retrieved.

Here, the list is grouped by department_id and sorted by both department_id and employee_name.

Through this overview, we can see how the LISTAGG function in the GBase8s database can help efficiently merge multiple rows into a string list, supporting sorting and custom delimiters. Whether in reporting, analysis, or data display, the LISTAGG function is highly useful. We hope this article enhances your understanding and usage of LISTAGG to improve database operation efficiency.

If you encounter any issues with the GBase database (GBase数据库), please contact our technical support team. We are here to help!