LIST (SQL)
Synopsis
LIST([ ALL | DISTINCT [BY(col-list)] ]
string-expr
[ %FOREACH(col-list) ] [ %AFTERHAVING] )
Arguments
Argument | Description |
---|---|
ALL | Optional — Specifies that LIST returns a list of all values for string-expr. This is the default if no keyword is specified. |
DISTINCT | Optional — A DISTINCT clause that specifies that LIST returns a list containing only the unique string-expr values. DISTINCT can specify a BY(col-list) subclause, where col-list can be a single field or a comma-separated list of fields. |
string-expr | An SQL expression that evaluates to a string. Usually the name of a column from the selected table. |
%FOREACH(col-list) | Optional — A column name or a comma-separated list of column names. See SELECT for further information on %FOREACH. |
%AFTERHAVING | Optional — Applies the condition found in the HAVING clause. |
Description
The LIST aggregate function returns a comma-separated list of the values in the specified column.
A simple LIST (or LIST ALL) returns a string containing a comma-separated list composed of all the values for string-expr in the selected rows. Rows where string-expr is the empty string ('') are represented by a placeholder comma in the comma-separated list. Rows where string-expr is NULL are not included in the comma-separated list. If there is only one string-expr value, and it is the empty string (''), LIST returns the empty string.
A LIST DISTINCT returns a string containing a comma-separated list composed of all the distinct (unique) values for string-expr in the selected rows: LIST(DISTINCT col1). The NULL string-expr is not included in the comma-separated list. LIST(DISTINCT BY(col2) col1) returns a comma-separated list containing only those col1 field values in records where the col2 values are distinct (unique). Note however that the distinct col2 values may include a single NULL as a distinct value.
Data Values Containing Commas
Because LIST uses commas to separate string-expr values, LIST should not be used for data values that contain commas. Use %DLIST or JSON_ARRAYAGG instead.
LIST and %SelectMode
You can use the %SelectMode property to specify the data display mode returned by LIST: 0=Logical (the default), 1=ODBC, 2=Display.
Note that LIST separates column values with commas, and ODBC mode separates elements within a %List column value with commas. Therefore, using ODBC mode when using LIST on a %List structure produces ambiguous results.
LIST and ORDER BY
The LIST function combines the values of a table column from multiple rows into a single comma-separated list of values. Because an ORDER BY clause is applied to the query result set after all aggregate fields are evaluated, ORDER BY cannot directly affect the sequence of values within this list. Under certain circumstances, LIST results may appear in sequential order, but this ordering should not be relied upon. The values listed within a given aggregate result value cannot be explicitly ordered.
Maximum LIST Size
Any LIST return value must be not longer than the maximum string length.
Related Aggregate Functions
-
LIST returns a comma-separated list of values.
-
%DLIST returns a list containing an element for each value.
-
JSON_ARRAYAGG returns a JSON array of values.
-
XMLAGG returns a concatenated string of values.
Examples
The following SQL example returns a host variable containing a comma-separated list of all of the values listed in the Home_State column of the Sample.Person table that start with the letter “A”:
SELECT LIST(Home_State) AS StateList
FROM Sample.Person
WHERE Home_State %STARTSWITH 'A'
Note that this list contains duplicate values.
The following SQL example returns a host variable containing a comma-separated list of all of the distinct (unique) values listed in the Home_State column of the Sample.Person table that start with the letter “A”:
SELECT LIST(DISTINCT Home_State) AS DistinctStates
FROM Sample.Person
WHERE Home_State %STARTSWITH 'A'
The following SQL example creates a comma-separated list of all of the values found in the Home_City column for each of the states, and a count of these city values by state. Every Home_State row contains a list of all of the Home_City values for that state. These lists may include duplicate city names:
SELECT Home_State,
COUNT(Home_City) AS CityCount,
LIST(Home_City) AS ListAllCities
FROM Sample.Person
GROUP BY Home_State
Perhaps more useful would be a comma-separated list of all of the distinct values found in the Home_City column for each of the states, as shown in the following example:
SELECT Home_State,
COUNT(DISTINCT Home_City) AS DistCityCount,
COUNT(Home_City) AS TotCityCount,
LIST(DISTINCT Home_City) AS DistCitiesList
FROM Sample.Person
GROUP BY Home_State
Note that this example returns integer counts of both the distinct city names and the total city names for each state.
The following example returns lists of Home_State values that begin with “A”. It returns the distinct Home_State values (DISTINCT Home_State); the Home_State values corresponding to distinct Home_City values (DISTINCT BY(Home_City) Home_State), which may possibly including one unique NULL for Home_City; and all Home_State values:
SELECT LIST(DISTINCT Home_State) AS DistStates,
LIST(DISTINCT BY(Home_City) Home_State) AS DistCityStates,
LIST(Home_State) AS AllStates
FROM Sample.Person
WHERE Home_State %STARTSWITH 'A'
The following Dynamic SQL example uses the %SelectMode property to specify the ODBC display mode for the list of values returned by the DOB date field:
SET myquery = "SELECT LIST(DOB) AS DOBs FROM Sample.Person WHERE Name %STARTSWITH 'A'"
SET tStatement = ##class(%SQL.Statement).%New()
SET tStatement.%SelectMode=1
SET qStatus = tStatement.%Prepare(myquery)
IF qStatus'=1 {WRITE "%Prepare failed:" DO $System.Status.DisplayError(qStatus) QUIT}
SET rset = tStatement.%Execute()
DO rset.%Display()
WRITE !,"End of data"
The following example uses the %FOREACH keyword. It returns a row for each distinct Home_State containing a list of age values for that Home_State:
SELECT DISTINCT Home_State,
LIST(Age %FOREACH(Home_State)) AgesForState
FROM Sample.Person WHERE Home_State %STARTSWITH 'M'
The following example uses the %AFTERHAVING keyword. It returns a row for each Home_State that contains at least one Name value that fulfills the HAVING clause condition (a name that begins with “M”). The first LIST function returns a list of all of the names for that state. The second LIST function returns a list containing only those names that fulfill the HAVING clause condition:
SELECT Home_State,
LIST(Name) AS AllNames,
LIST(Name %AFTERHAVING) AS HavingClauseNames
FROM Sample.Person
GROUP BY Home_State
HAVING Name LIKE 'M%'
ORDER BY Home_State
See Also
-
Aggregate Functions overview
-
%DLIST aggregate function
-
JSON_ARRAYAGG aggregate function
-
XMLAGG aggregate function
-
SELECT statement