The GROUP_CONCAT and LISTAGG functions

The GROUP_CONCAT function and its synonym LISTAGG is an aggregate function that returns a concatenated, non-NULL, string value from a selected group of rows.

It can be used for combining and displaying related, aggregated data in a compact format based on a certain column or attribute values in association with a multi-row select query.

The primary purpose of the GROUP_CONCAT and LISTAGG functions is to concatenate values from multiple rows into a single string. It will then display a single representative value that summarizes the data from one field across multiple rows.

When concatenating values, the joining character is specified as the SEPARATOR parameter. A NULL separator is interpreted as a zero length blank space.

Figure 1. Query

GROUP_CONCAT(
     expression, separator
)
LISTAGG(
     expression, separator
)
Figure 2. Example
SELECT GROUP_CONCAT (tabname, ',') tables
      FROM systables WHERE tabid < 8;
       tables  systables,syscolumns,sysindices,systabauth,syscolauth,sysviews,sysusers