GROUP_CONCAT Function

An aggregate function that returns a single string representing the argument value concatenated together for each row of the result set. If the optional separator string is specified, the separator is added between each pair of concatenated values. The default separator is a comma followed by a space.

Syntax:

  1. GROUP_CONCAT([ALL | DISTINCT] expression [, separator])

Usage notes: concat() and concat_ws() are appropriate for concatenating the values of multiple columns within the same row, while group_concat() joins together values from different rows.

By default, returns a single string covering the whole result set. To include other columns or values in the result set, or to produce multiple concatenated strings for subsets of rows, include a GROUP BY clause in the query.

Return type: STRING

This function cannot be used in an analytic context. That is, the OVER() clause is not allowed at all with this function.

Currently, Impala returns an error if the result value grows larger than 1 GiB.

Examples:

The following examples illustrate various aspects of the GROUP_CONCAT() function.

You can call the function directly on a STRING column. To use it with a numeric column, cast the value to STRING.

  1. [localhost:21000] > create table t1 (x int, s string);
  2. [localhost:21000] > insert into t1 values (1, "one"), (3, "three"), (2, "two"), (1, "one");
  3. [localhost:21000] > select group_concat(s) from t1;
  4. +----------------------+
  5. | group_concat(s)      |
  6. +----------------------+
  7. | one, three, two, one |
  8. +----------------------+
  9. [localhost:21000] > select group_concat(cast(x as string)) from t1;
  10. +---------------------------------+
  11. | group_concat(cast(x as string)) |
  12. +---------------------------------+
  13. | 1, 3, 2, 1                      |
  14. +---------------------------------+

Specify the DISTINCT keyword to eliminate duplicate values from the concatenated result:

  2. [localhost:21000] > select group_concat(distinct s) from t1;
  3. +--------------------------+
  4. | group_concat(distinct s) |
  5. +--------------------------+
  6. | three, two, one          |
  7. +--------------------------+

The optional separator lets you format the result in flexible ways. The separator can be an arbitrary string expression, not just a single character.

  1. [localhost:21000] > select group_concat(s,"|") from t1;
  2. +----------------------+
  3. | group_concat(s, '|') |
  4. +----------------------+
  5. | one|three|two|one    |
  6. +----------------------+
  7. [localhost:21000] > select group_concat(s,'---') from t1;
  8. +-------------------------+
  9. | group_concat(s, '---')  |
  10. +-------------------------+
  11. | one---three---two---one |
  12. +-------------------------+

The default separator is a comma followed by a space. To get a comma-delimited result without extra spaces, specify a delimiter character that is only a comma.

  1. [localhost:21000] > select group_concat(s,',') from t1;
  2. +----------------------+
  3. | group_concat(s, ',') |
  4. +----------------------+
  5. | one,three,two,one    |
  6. +----------------------+

Including a GROUP BY clause lets you produce a different concatenated result for each group in the result set. In this example, the only X value that occurs more than once is 1, so that is the only row in the result set where GROUP_CONCAT() returns a delimited value. For groups containing a single value, GROUP_CONCAT() returns the original value of its STRING argument.

  1. [localhost:21000] > select x, group_concat(s) from t1 group by x;
  2. +---+-----------------+
  3. | x | group_concat(s) |
  4. +---+-----------------+
  5. | 2 | two             |
  6. | 3 | three           |
  7. | 1 | one, one        |
  8. +---+-----------------+

Parent topic: Impala Aggregate Functions