Examples of STRING_AGG Function to Concatenate Strings
Example 1: Group According to Values
The following query groups the table according to the values in the field ‘val1’ and then uses STRING_AGG() to concat the values of ‘val2‘.
Query:
SELECT val1, STRING_AGG(val2, ',') as val2
FROM test
GROUP BY val1;
Output:
Output
Explanation: This query groups rows from the test table by the val1 column and concatenates values from the val2 column for each group and separated by commas. The result set includes unique values from val1 and their concatenated val2 values.
Example 2: Group According to Values in Ascending Order
The following query is similar to the query performed in the above examples. The only difference is that it orders the values in ‘val2’ in ascending order by making use of the optional ORDER BY clause before concatenating.
Query:
SELECT val1, STRING_AGG(val2, ',' ORDER BY CAST(val2 AS INT)) as val2
FROM test
GROUP BY val1;
Output:
Output
Explanation: This query is similar to the previous one, but it adds an ORDER BY clause within the STRING_AGG function. It first converts the val2 values to integers using CAST and then orders them in ascending order before concatenating them. The result set will contain val1 values and their val2 values concatenated and sorted numerically, separated by commas.
Example 3: Group the Data and Concatenates Values
Like the query presented in example 1, The following query groups the data by ‘val1’ and concatenates values of ‘val2’. The only difference it is that it uses ‘/’ as the separator.
Query:
SELECT val1, STRING_AGG(val2, '/') as val2
FROM test
GROUP BY val1;
Output:
Output
Explanation: This query groups rows from the test table by the val1 column and concatenates values from the val2 column for each group, separated by slashes (/). The result set includes unique values from val1 and their concatenated val2 values.
How to Use STRING_AGG to Concatenate Strings in PostgreSQL?
In database management, aggregating and concatenating strings is a common requirement. PostgreSQL provides a powerful solution for this with the STRING_AGG function. This article explores how to leverage STRING_AGG to concatenate strings in PostgreSQL efficiently, offering multiple approaches to cater to various situations. In this article, we will understand how to use STRING_AGG effectively with the help of various examples and so on.