GroupConcat() mvSQL database function in Jitterbit App Builder
Summary
Allows you to aggregate a column in the context of a group by, for example creating a comma delimited list. You can optionally use the ORDER BY clause with this function to sort the values in ascending or descending order before concatenating. This works similarly to the Transact-SQL function STRING_AGG.
Important
SQLite does not support ORDER BY, and that limitation affects any vendor that relies on post-processing done in-memory, for example: REST
Syntax
GROUPCONCAT(<column 1>, <separator string>)
GROUPCONCAT(<column 1>, <separator string> ORDER BY <column 2>)
Parameters
| Parameter | Description | Notes |
|---|---|---|
| Column 1 | The column to aggregate. | |
| Separator string | The string used as a separator for concatenated strings from the first argument. | |
| Column 2 | The column to sort the values by before concatenating. This represents one or more columns. | Optional. If used, must be preceeded by ORDER BY (see examples below). |
Returns
A concatenated string of the aggregated values with the separator between them. If ORDER BY is used, results will be sorted accordingly.
Examples
For the following examples, we will assume the following records exist:
- Peter Able
- Martha Betters
- Nathalia Kennedy
| Example | Returns |
|---|---|
GROUPCONCAT(FirstName, ',') | Nathalia, Peter, Martha |
GROUPCONCAT(FirstName, ';' ORDER BY LastName) | Peter; Martha; Nathalia |