Skip to Content

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> ORDER BY <column 2>)

Parameters

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.

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 1:

GROUPCONCAT(FirstName, ',')

Returns

Nathalia, Peter, Martha

Example 2:

GROUPCONCAT(FirstName, ';' ORDER BY LastName)

Returns

Peter; Martha; Nathalia