%DLIST (SQL)

Synopsis

%DLIST([ALL | DISTINCT [BY(col-list)]] 
  string-expr 
  [%FOREACH(col-list)] [%AFTERHAVING])

Description

The %DLIST aggregate function returns an ObjectScript %List structure containing the values in the specified column as list elements.

A simple %DLIST (or %DLIST ALL) returns InterSystems IRIS list composed of all the non-NULL values for string-expr in the selected rows. Rows where string-expr is NULL are not included as elements in the list structure.

A %DLIST DISTINCT returns an InterSystems IRIS list composed of all the distinct (unique) non-NULL values for string-expr in the selected rows: %DLIST(DISTINCT col1). NULL is not included as an element in the %List structure. %DLIST(DISTINCT BY(col2) col1) returns a %List of elements including 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.

For further information about InterSystems IRIS list structures, see $LIST and related functions.

Arguments

Examples

The following example returns an InterSystems IRIS list of all of the values listed in the Home_State column of the Sample.Person table that start with the letter “A”:

SELECT %DLIST(Home_State)
FROM Sample.Person
WHERE Home_State %STARTSWITH 'A'

Note that this InterSystems IRIS list contains elements with duplicate values.

The following example returns an InterSystems IRIS 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 %DLIST(DISTINCT Home_State)
FROM Sample.Person
WHERE Home_State %STARTSWITH 'A'

The following example creates an InterSystems IRIS 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,
       %DLIST(Home_City) AS AllCities,
       COUNT(Home_City) AS CityCount
FROM Sample.Person
GROUP BY Home_State

Perhaps more useful would be a 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,
       %DLIST(DISTINCT Home_City) AS CitiesList,
       COUNT(DISTINCT Home_City) AS DistinctCities,
       COUNT(Home_City) AS TotalCities
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 %List structures of Home_State values that begin with “A”. It returns the following as %List elements: 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 %DLIST(DISTINCT Home_State) AS DistStates,
       %DLIST(DISTINCT BY(Home_City) Home_State) AS DistCityStates,
       %DLIST(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 structure FavoriteColors date field. ODBC mode returns the value for each column as a comma-separated list, and the $LISTTOSTRING function specifies a different delimiter (in this example, ||) to separate the values from the different columns:

  set myquery = "SELECT %DLIST(FavoriteColors) AS colors FROM Sample.Person WHERE Name %STARTSWITH 'A'"
  set tStatement = ##class(%SQL.Statement).%New()
  set tStatement.%SelectMode=1

  set qStatus = tStatement.%Prepare(myquery)
  if $$$ISERR(qStatus) {write "%Prepare failed:" do $SYSTEM.Status.DisplayError(qStatus) quit}
  
  set rset = tStatement.%Execute()
  if (rset.%SQLCODE '= 0) {write "%Execute failed:", !, "SQLCODE ", rset.%SQLCODE, ": ", rset.%Message quit}

  while rset.%Next()
  {
    write $LISTTOSTRING(rset.colors,"||"),!
  }
  if (rset.%SQLCODE < 0) {write "%Next failed:", !, "SQLCODE ", rset.%SQLCODE, ": ", rset.%Message quit}

  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 %DLIST function returns a list of all of the names for that state. The second %DLIST function returns a list containing only those names that fulfill the HAVING clause condition:

SELECT Home_State,
       %DLIST(Name) AS AllNames,
       %DLIST(Name %AFTERHAVING) AS HaveClauseNames
    FROM Sample.Person
    GROUP BY Home_State
    HAVING Name LIKE 'M%'
    ORDER BY Home_state

See Also

• Aggregate Functions overview

• SELECT

• $LIST function

• JSON_ARRAYAGG aggregate function

• LIST aggregate function

• XMLAGG aggregate function