Caché SQL Reference
LIST
[Back] [Next]
   
Server:docs2
Instance:LATEST
User:UnknownUser
 
-
Go to:
Search:    

An aggregate function that creates a comma-separated list of values.
Synopsis
LIST([ALL | DISTINCT] string-expr [%FOREACH(col-list)] [%AFTERHAVING])
Arguments
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 — Specifies that LIST returns a list containing only the unique string-expr values. If not specified, the default is ALL.
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 different (unique) values for string-expr in the selected rows. The NULL string-expr is not included in the comma-separated list.
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 comma-separated list of values are, by default, listed in the RowId sequence. You can use an ORDER BY clause to sequence these comma-separated values in ascending collation sequence. This use of ORDER BY is subject to various conditions and restrictions, as described in the With ORDER BY section of the Aggregate Functions overview page.
Maximum LIST Size
The largest permitted LIST return value is the maximum string length configured for your system. If your system is configured for Long Strings (the default), the longest list is 3,641,144 characters.
Related Aggregate Functions
Examples
The following Embedded 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”:
  &sql(SELECT LIST(Home_State)
       INTO :statelist
       FROM Sample.Person
       WHERE Home_State %STARTSWITH 'A')
  WRITE "The states are:",!,statelist
 
Note that this list contains duplicate values.
The following Embedded 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”:
  &sql(SELECT LIST(DISTINCT Home_State)
       INTO :statelist
       FROM Sample.Person
       WHERE Home_State %STARTSWITH 'A')
  WRITE "The distinct states are:",!,statelist
 
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 Dynamic SQL example uses the %SelectMode property to specify the ODBC display mode for the list of values returned by the DOB date field:
  ZNSPACE "SAMPLES"
  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 Dynamic SQL example uses the %FOREACH keyword. It returns a row for each distinct Home_State containing a list of age values for that Home_State:
  ZNSPACE "SAMPLES"
  SET myquery = 3
  SET myquery(1) = "SELECT DISTINCT Home_State,"
  SET myquery(2) = "LIST(Age %FOREACH(Home_State)) AgesForState "
  SET myquery(3) = "FROM Sample.Person WHERE Home_State %STARTSWITH 'M'"
  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 %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