One thing that I often have to do is convert a subquery into a string that is delimited by a comma. For example, I may need to write a quick report that shows the users’ names, their login ID, and all of the roles that they fulfill. In this case, there may be three tables to reference: users, roles, and user_role_xref. In order to display the desired data for all enabled users, I can use the following query in PostgreSQL:
SELECT users.user_id, users.full_name, array_to_string(array( SELECT roles.title FROM user_role_xref, roles WHERE user_role_xref.user_id = users.id AND user_role_xref.role_id = roles.id ), ', ') AS roles FROM users WHERE users.enabled = TRUE;
It is really as simple as that. All you have to do is convert your subquery into an array and then use the
array_to_string function to join each element element together with the second parameter used as the delimiter.