Returns a comma-delimited list of values.
LIST( [ DISTINCT ] string-expression [, delimiter-string ] [ ORDER BY order-by-expression [ ASC | DESC ], ... ] )
string-expression A string expression, usually a column name. For each row in the column, the value is added to a comma-separated list. When DISTINCT is specified, only unique 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. Also, multiple LIST functions in the same query block are not allowed to use different order-by-expression arguments.
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.
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.
SQL/2003 Vendor extension.
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 )|
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'
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;
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;
|Discuss this page in DocCommentXchange.
Send feedback about this page using email.
|Copyright © 2009, iAnywhere Solutions, Inc. - SQL Anywhere 11.0.1|