In databases, users may encounter a situation where query’s each value is supposed to be output in a single row with a specific delimiter. For this purpose, the Postgres STRING_AGG() function can be utilized. The PostgreSQL users can transform a string or a query result into a comma-separated list using a built-in aggregate function named STRING_AGG().

This post will demonstrate how to get query results as a Comma Separated List in Postgres.

How to Get Query Results as a Comma-Separated List in PostgreSQL?

To get the query results as a comma-separated list, all you need to do is pass the query to be aggregated as an argument to the STRING_AGG() function. Moreover, you are also required to specify the delimiter or separator:

STRING_AGG(expression, separator);

The expression can be a table column while the separator can be anything like a string, character, or symbol.

Example1: Getting Query Result as a List of Comma-Separated Values

We have a sample table named “product_details” with the following records:

SELECT * FROM product_details;

Now, we will execute the SELECT query with the STRING_AGG() function to get the query result as a comma-separated list:

SELECT STRING_AGG(pro_name, ',') AS products
FROM product_details;

The above snippet depicts that the query results have been successfully retrieved as a comma-separated list.

Example2: Grouping Query Result as a List of Comma-Separated Values

Mostly, the STRING_AGG() function is used with the GROUP BY clause to group the query result as a comma-separated list:

SELECT pro_quantity, STRING_AGG(pro_name, ',') AS products
FROM product_details
GROUP BY pro_quantity;

The output shows that the products are grouped based on the “pro_quantity” column and each group contains a list of comma-separated products.

That was all about getting the query results as a comma-separated list in Postgres.

Conclusion

The PostgreSQL users can transform a string or a query result into a comma-separated list using a built-in aggregate function named STRING_AGG(). For this purpose, all you need to do is pass the query to be aggregated as a first argument and a delimiter as a second argument to the STRING_AGG() function. This post has explained a detailed procedure for getting the results as a comma-separated list in Postgres.