[postgis-users] spatial join and summarize, list all names

[Mike Toews]

On 24 March 2013 01:46, Stephen Woodbridge <woodbri at swoodbridge.com> wrote:
>
> How about something like:
>
> SELECT p.p_name,
>     array_to_string(ARRAY_AGG(Grade.Mark),';') As marks
> FROM Student
> LEFT JOIN Grade ON Grade.Student_id = Student.Id
> GROUP BY Student.Name;
>
> Aggregate into an array and then convert the array to a string. Look at the
> array functions in the manual for more information.

Even simpler, use string_agg(expression, delimiter) to get a delimited
text result, e.g., string_agg(Grade.Mark, ', ')

Also experiment using DISTINCT an ORDER BY within the aggregate expression.

http://www.postgresql.org/docs/current/static/functions-aggregate.html

-Mike