I found a need the other day for a function similar to the mysql 'group_concat' function.

I did a web-search and found an example but I didn't quite understand it so here's my attempt :)

First of all, we need the table and some data:

create table example(id int, val char(1));
insert into example(id, val) values (1, 'a');
insert into example(id, val) values (2, 'b');
insert into example(id, val) values (2, 'c');

The query looks rather complicated but here's the basics:

select array_to_string (
array(
select val from example
), ','
);


The inside part of the query turns the 'val' entries from the table into an array, then we use the 'array_to_string' function to turn it into one big string.

We end up with:

db=# select array_to_string (
db(# array(
db(# select val from example order by val
db(# ), ','
db(# );
array_to_string
-----------------
a,b,c
(1 row)


That puts all of the values from the table in an alphabetical order and in one long string.

To turn it into the output that the other example had, we need to do a bit more.

The subquery has to do a join based on the outside id (which happens to be a self join - ie back to the same database table), then we group & order the results together.

select id,
count(outside_example.*),
array_to_string (
array(
select val from example inside WHERE inside.id=outside_example.id
), ','
)
FROM example outside_example
GROUP BY outside_example.id
ORDER BY outside_example.id
;

That results in this being displayed:

 id | count | array_to_string

----+-------+-----------------

  1 |     1 | a

  2 |     2 | b,c

Which tells us for id '1' there is 1 value called 'a'.

For id '2' there are 2 values and those values are 'b' and 'c'.