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.

Categories: BlogSQL

8 Comments

Fabio Telles · July 15, 2011 at 6:12 AM

Another way (but also using arrays) could by using an user defined aggregate like in: http://www.postgresql.org/docs/current/static/xaggr.html

– look at ‘array_accum’ example.

Thom Brown · July 27, 2011 at 1:48 PM

As of 9.0 you can also use string_agg: http://www.postgresql.org/docs/9.0/static/functions-aggregate.html

Ted · August 8, 2011 at 2:08 PM

Truly interesting blog Kudos

John Payne · May 9, 2016 at 7:02 PM

Thanks–that’s useful! I often find myself wanting a comma-separated list of column names. This can be gotten using the syntax that you suggested:
SELECT array_to_string(array(SELECT column_name::text FROM information_schema.columns WHERE table_name=’mytable’),’,’)

Jádson · September 2, 2016 at 4:39 PM

Thank you! Helped me a lot.

vivian · October 16, 2016 at 8:39 PM

You are a life saver! This was exactly what I was looking for 🙂

Stephen · April 4, 2017 at 7:56 AM

Great solution!

postgresql中将多行记录合并为一个字符串 | LiangXu Wang · December 26, 2012 at 1:07 AM

[…] http://gotochriswest.com/blog/2011/07/14/postgresql-converting-rows-into-a-string/ 本条目发布于 2012-12-26。属于 postgresql 分类,被贴了 […]

Leave a Reply

Your email address will not be published. Required fields are marked *