Postgresql GROUP_CONCAT equivalent?

sql postgresql group-concat string-aggregation
TwixxyKit picture TwixxyKit · Apr 1, 2010 · Viewed 157.5k times · Source

I have a table and I'd like to pull one row per id with field values concatenated.

In my table, for example, I have this:

TM67 | 4  | 32556
TM67 | 9  | 98200
TM67 | 72 | 22300
TM99 | 2  | 23009
TM99 | 3  | 11200

And I'd like to output:

TM67 | 4,9,72 | 32556,98200,22300
TM99 | 2,3    | 23009,11200

In MySQL I was able to use the aggregate function GROUP_CONCAT, but that doesn't seem to work here... Is there an equivalent for PostgreSQL, or another way to accomplish this?

Answer

a_horse_with_no_name picture a_horse_with_no_name · Jan 10, 2012

Since 9.0 this is even easier:

SELECT id, 
       string_agg(some_column, ',')
FROM the_table
GROUP BY id

Related questions

Select first row in each GROUP BY group?

As the title suggests, I'd like to select the first row of each set of rows grouped with a GROUP BY. Specifically, if I've got a purchases table that looks like this: SELECT * FROM purchases; My Output: id | customer | total …

Read More
How do I (or can I) SELECT DISTINCT on multiple columns?

I need to retrieve all rows from a table where 2 columns combined are all different. So I want all the sales that do not have any other sales that happened on the same day for the same price. The sales …

Read More
How to select the nth row in a SQL database table?

I'm interested in learning some (ideally) database agnostic ways of selecting the nth row from a database table. It would also be interesting to see how this can be achieved using the native functionality of the following databases: SQL Server …

Read More