ListAggDistinct
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.