How to Concatenate Strings Within a PostgreSQL GROUP BY Query?

How to concatenate strings in PostgreSQL's GROUP BY query?

Question:

In PostgreSQL, how to concatenate strings of fields in a GROUP BY query?

Example:

Consider a table with the following schema:

ID	COMPANY_ID	EMPLOYEE
1	1	Anna
2	1	Bill
3	2	Carol
4	2	Dave

The goal is to group by COMPANY_ID and concatenate the EMPLOYEE values, resulting in the following:

COMPANY_ID	EMPLOYEE
1	Anna, Bill
2	Carol, Dave

Solution:

PostgreSQL 9.0 or higher:

• Use the string_agg(expression, delimiter) function:

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

PostgreSQL 8.4.x:

• Use the array_agg(expression) function to combine array_to_string():

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

PostgreSQL 8.3.x and earlier:

• Create a custom aggregate function to concatenate strings:

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

SELECT company_id, textcat_all(employee)
FROM mytable
GROUP BY company_id;</code>

• Optionally, you can create a function to customize the join behavior, such as adding separators.

The above is the detailed content of How to Concatenate Strings Within a PostgreSQL GROUP BY Query?. For more information, please follow other related articles on the PHP Chinese website!