Click here to view and discuss this page in DocCommentXchange. In the future, you will be sent there automatically.

SQL Anywhere 10.0.1 » SQL Anywhere Server - SQL Reference » SQL Functions » Alphabetical list of functions

LESSER function [Miscellaneous] Next Page

LIST function [Aggregate]


Returns a comma-delimited list of values.

Syntax

LIST(
{ string-expression | DISTINCT string-expression }
[, delimiter-string ]
[ ORDER BY order-by-expression ] )

Parameters

string-expression    A string, usually a column name. For each row, the expression's value is added to the comma-separated list.

DISTINCT string-expression    An expression; for example, the name of a column that you are using in the query. For each unique value of that column, the value is added to the comma-separated list.

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.

Multiple LIST functions in the same query block are not allowed to use different order-by-expression arguments.

Remarks

NULL values are not added to the list. LIST ( X ) returns the concatenation (with delimiters) of all the non-NULL values of X for each row in the group. If there does not exist at least one row in the group with a definite X-value, then LIST( X ) returns the empty string.

If both DISTINCT and ORDER BY are supplied, the DISTINCT expression must be the same as the ORDER BY expression.

A LIST function cannot be used as a window function, but it can be used as input to a window function.

This function supports NCHAR inputs and/or outputs.

Standards and compatibility
Examples

The following statement returns the value 487 Kennedy Court, 547 School Street.

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

The following statement lists employee IDs. Each row in the result set contains a comma-delimited list of employee IDs for a single department.

SELECT LIST( EmployeeID )
FROM Employees
GROUP BY DepartmentID;
LIST( EmployeeID )
102,105,160,243,247,249,266,278,...
129,195,299,467,641,667,690,856,...
148,390,586,757,879,1293,1336,...
184,207,318,409,591,888,992,1062,...
191,703,750,868,921,1013,1570,...

The following statement sorts the employee IDs by the last name of the employee:

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

Sorted IDs '1751,591,1062,1191,992,888,318,184,1576,207,1684,1643,1607,1740,409,1507'

Sorted IDs
1013,191,750,921,868,1658,...
1751,591,1062,1191,992,888,318,...
1336,879,586,390,757,148,1483,...
1039,129,1142,195,667,1162,902,...
160,105,1250,247,266,249,445,...

The following statement returns semicolon-separated lists. Note the position of the ORDER BY clause and the list separator:

SELECT LIST( EmployeeID, ';' ORDER BY Surname ) AS "Sorted IDs"
FROM Employees
GROUP BY DepartmentID;
Sorted IDs
1013;191;750;921;868;1658;703;...
1751;591;1062;1191;992;888;318;...
1336;879;586;390;757;148;1483;...
1039;129;1142;195;667;1162;902; ...
160;105;1250;247;266;249;445;...

Be sure to distinguish the previous statement from the following statement, which returns comma-separated lists of employee IDs sorted by a compound sort-key of ( Surname, ';' ):

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