ASA SQL Reference
  SQL Functions
    Alphabetical list of functions

LIST function [Aggregate]

Function 

Returns a comma-separated list of values.

Syntax 

LIST (
{ string-expression | DISTINCT column-name }
[ , 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 column-name    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.

Usage 

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.

Standards and compatibility 

• SQL/92    Vendor extension.

• SQL/99    Vendor extension.

• Sybase    Not supported in Adaptive Server Enterprise.

Examples 

The following statement returns the value 48 Kennedy Court, 54 School Street.

SELECT LIST( street ) FROM employee
WHERE emp_fname = 'Thomas'

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

SELECT LIST( emp_id )
FROM employee
GROUP BY dept_id

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

SELECT LIST( emp_id ORDER BY emp_lname ) AS "Sorted IDs"
FROM EMPLOYEE
GROUP BY dept_id

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

SELECT LIST( emp_id, ';' ORDER BY emp_lname ) AS "Sorted IDs"
FROM EMPLOYEE
GROUP BY dept_id

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 ( emp_lname, ';' ):

SELECT LIST( emp_id ORDER BY emp_lname, ';' ) AS "Sorted IDs"
FROM EMPLOYEE
GROUP BY dept_id