LISTAGG

Oracle

LISTAGG(measure_expr [, ‘delimiter’]) WITHIN GROUP (order_by_clause) [OVER (query_partition_clause)]

• measure_expr: This parameter identifies the column or expression which you want to concatenate into a string.
• ‘delimiter’: This optional parameter is the string that separates the values within the combined string. If you omit the ‘delimiter’, Oracle uses a null string.
• order_by_clause: This parameter determines the order of the elements in the concatenated string made from measure_expr values. It’s used with WITHIN GROUP clause.
• query_partition_clause: This optional parameter is part of the OVER clause. It allows you to partition the rows into groups for the LISTAGG function to act upon.

Example

SELECT department_id,
   LISTAGG(employee_id, ',') WITHIN GROUP (ORDER BY employee_id)
   AS "Employee_List"
FROM employees
GROUP BY department_id;

Output

DEPARTMENT_ID | Employee_List
--------------|----------------
10            | 200,201
20            | 202,203,204
30            | 205

Explanation

The LISTAGG() function aggregates the ‘employee_id’ from the ‘employees’ table into a list separated by commas. The WITHIN GROUP clause arranges ‘employee_id’ in an ascending order before aggregation. The entire operation is grouped by ‘department_id’, resulting in a list of employees for each department.

SQL Server

LISTAGG([DISTINCT] expression, separator [WITHIN GROUP (ORDER BY expression [ASC | DESC] [NULLS {FIRST | LAST}])])

• distinct: This parameter is optional and is used to eliminate duplicate values from the specified expression before concatenation.
• expression: This is the column or expression that needs to be concatenated. It specifies the column on which LISTAGG will operate.
• separator: This defines the delimiter that will be inserted between each expression result in the output.
• within group: This optional clause allows specific ordering of the values in the result.
• order by expression: Inside the WITHIN GROUP clause, this is used for sorting the input rows before they are concatenated together. You can specify multiple expressions separated by commas.
• asc | desc: Also within the WITHIN GROUP clause, this optional parameter specifies the order in which the ORDER BY results are sorted, either in ascending (ASC) or descending (DESC) order.
• nulls first | last: This optional parameter indicates the position of null values in the ordered result set. NULLS FIRST places nulls at the beginning, while NULLS LAST places them at the end.

Example

SELECT Department,
       LISTAGG(Employee, ',') WITHIN GROUP (ORDER BY Employee) AS Employees
FROM   EmployeeDepartments
GROUP BY Department;

Output

| Department | Employees               |
|------------|-------------------------|
| HR         | John, Lisa, Samantha    |
| IT         | Jake, Katie, Tom        |
| Marketing  | Mary, Robert, Stephanie |

Explanation

This example uses the LISTAGG function in SQL Server to concatenate the names of employees in each department, separated by commas. The WITHIN GROUP clause sorts the employees by their names before concatenating them. A result set is returned that lists each department along with a list of its employees.