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:
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:
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:
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.
Contact Us