How Can I Concatenate Values Across Multiple Rows in PostgreSQL?

PostgreSQL data row value connection

In relational database operations, it is often necessary to process the data before obtaining the final result. A common task is to concatenate values ​​from multiple rows based on a common identifier. In PostgreSQL, the GROUP_CONCAT function provides a simple solution for this.

GROUP_CONCAT Function

PostgreSQL version 9.0 and above introduced the GROUP_CONCATaggregation function, which takes a set of values ​​in a column and concatenates them into a single string. The syntax of GROUP_CONCAT is as follows:

<code class="language-sql">GROUP_CONCAT(DISTINCT expression [ORDER BY expression] [separator])</code>

Parameters:

• DISTINCT: (optional) Prevents duplicate values ​​from being included in the results.
• expression: The column or expression to join.
• ORDER BY: (optional) Specifies the order in which values ​​are concatenated.
• separator: (optional) Character or string used to separate concatenated values. The default is comma (,).

Example

Consider the following table named "sample_data":

id	value
TM67	4
TM67	9
TM67	72
TM99	2
TM99	3

To join the values ​​in the "value" column for each unique "id" we can use GROUP_CONCAT like this:

<code class="language-sql">SELECT id, GROUP_CONCAT(value) AS concatenated_values
FROM sample_data
GROUP BY id;</code>

This query will produce the following output:

id	concatenated_values
TM67	4,9,72
TM99	2,3

As you can see, the values ​​for each "id" are concatenated and separated by commas.

Alternative syntax for PostgreSQL 9.0

Starting with PostgreSQL version 9.0, an alternative syntax for GROUP_CONCAT can be used:

<code class="language-sql">SELECT id, string_agg(value, ',') AS concatenated_values
FROM sample_data
GROUP BY id;</code>

This syntax is equivalent to the GROUP_CONCAT syntax described above.

Conclusion

You can easily join multiple rows of data in PostgreSQL by using the GROUP_CONCAT or string_agg function. Which syntax you choose depends on your PostgreSQL version and personal preference.

The above is the detailed content of How Can I Concatenate Values Across Multiple Rows in PostgreSQL?. For more information, please follow other related articles on the PHP Chinese website!