LIST function [Aggregate]

LIST(
[ALL | DISTINCT] string-expresssion
[, 'delimiter-string']
[ORDER BY order-by-expression [ ASC | DESC ], ... ] )

• string-expression – A string expression, usually a column name. When ALL is specified (the default), for each row in the group, the value of string-expression is added to the result string, with values separated by delimiter-string. When DISTINCT is specified, only unique string-expression values are added.
• delimiter-string – A delimiter string for the list items. The default setting is a comma. There is no delimiter if a value of NULL or an empty string is supplied. The delimiter-string must be a constant.
• order-by-expression – Order the items returned by the function. There is no comma preceding this argument, which makes it easy to use in the case where no delimiter-string is supplied.

  order-by-expression cannot be an integer literal. However, it can be a variable that contains an integer literal.

  When an ORDER BY clause contains constants, they are interpreted by the optimizer and then replaced by an equivalent ORDER BY clause. For example, the optimizer interprets ORDER BY 'a' as ORDER BY expression.

  A query block containing more than one aggregate function with valid ORDER BY clauses can be executed if the ORDER BY clauses can be logically combined into a single ORDER BY clause. For example, the following clauses:

   ORDER BY expression1, 'a', expression2 

   ORDER BY expression1, 'b', expression2, 'c', expression3 

  are subsumed by the clause:

   ORDER BY expression1, expression2, expression3 

SELECT LIST( Street ) FROM Employees
WHERE GivenName = 'Thomas'; 

SELECT LIST( EmployeeID )
FROM Employees
GROUP BY DepartmentID; 

SELECT LIST( EmployeeID ORDER BY Surname ) AS "Sorted IDs"
FROM Employees
GROUP BY DepartmentID; 

SELECT LIST( EmployeeID, ';' ORDER BY Surname ) AS "Sorted IDs"
FROM Employees
GROUP BY DepartmentID; 

SELECT LIST( EmployeeID ORDER BY Surname, ';' ) AS "Sorted IDs"
FROM Employees
GROUP BY DepartmentID;