The ListAgg function joins a given column's distinct row values into a single list. Each window of values within a column will be aggregated into an individual list. The output of this function is a text column.


Usage

ListAggDistinct([Column], separator, direction)

Function Arguments:

  • [Column] (required) - The column of values that you would like to combine.
  • separator (optional) - The separator to use between values. If no separator is specified, a comma will be used.
  • direction (optional) - The sort direction to use for the list. Either 'asc' or 'desc'. I no direction is specified, the values will be sorted in ascending order. 

Example

ListAggDistinct([Store Name])

A table contains a grouped [Store State] column.  Outside of the grouping, there are orders for stores within the state.  ListAggDistinct can be used to return a list of distinct store names within each state.  Here, the separator and direction are not specified, so the output will be separated by a comma and will be sorted ascending.

ListAggDistinct([Store Name], "-")

Here, a dash is indicated as the separator, so each distinct value will be separated by a dash.  

ListAggDistinct([Store Name], "-", "desc")

Here, the direction is specified as descending, so the output will be sorted descending.

ListAggDistinct([Store State], "\n")

Here, the special keyword for a newline is indicated as the separator so each value will be placed on its own line. Note that you must wrap the column's text for this to occur.

Screenshot

See Also

ListAgg
Concat


Was this page helpful?
Yes No