How to Concatenate Strings from a Grouped Field in PostgreSQL?

In PostgreSQL databases, it is a common requirement to group by a specific field and concatenate the value of another string field in each group. The implementation method depends on the PostgreSQL version.

PostgreSQL 9.0 and above

PostgreSQL 9.0 and above (since 2010) provides the string_agg(expression, delimiter) function for this purpose. To concatenate the strings in a string field named EMPLOYEE, you can use the following query:

<code class="language-sql">SELECT company_id, string_agg(employee, ', ')
FROM mytable
GROUP BY company_id;</code>

You can also specify a ORDER BY clause in an aggregate expression to control the order in which values ​​are concatenated.

PostgreSQL version 8.4

PostgreSQL 8.4 introduced the array_agg(expression)aggregation function, which collects values ​​into an array. To get the concatenated string you can use the array_to_string() function:

<code class="language-sql">SELECT company_id, array_to_string(array_agg(employee), ', ')
FROM mytable
GROUP BY company_id;</code>

PostgreSQL 8.3 and earlier

Prior to PostgreSQL 8.4, there was no built-in aggregate function to concatenate strings. Custom aggregate functions can be created using the CREATE AGGREGATE statement:

<code class="language-sql">CREATE AGGREGATE textcat_all(
  basetype    = text,
  sfunc       = textcat,
  stype       = text,
  initcond    = ''
);</code>

This basic aggregate function simply concatenates all the strings in the group. You can also create a custom function that adds a separator between concatenated values, for example:

<code class="language-sql">CREATE FUNCTION commacat(acc text, instr text) RETURNS text AS $$
  BEGIN
    IF acc IS NULL OR acc = '' THEN
      RETURN instr;
    ELSE
      RETURN acc || ', ' || instr;
    END IF;
  END;
$$ LANGUAGE plpgsql;</code>

You can then use a custom aggregate function in a GROUP BY query to concatenate the strings:

<code class="language-sql">SELECT company_id, textcat_all(commacat(NULL, employee)) AS concatenated_employees
FROM mytable
GROUP BY company_id;</code>

The above is the detailed content of How to Concatenate Strings from a Grouped Field in PostgreSQL?. For more information, please follow other related articles on the PHP Chinese website!