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.
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 分类,被贴了 […]