Postgres Pro
Downloads
Home   >   mailing lists

Thread: Combining data in different rows?

Combining data in different rows?

From
Anthony Masinton
Date:
I would like to combine data from different rows in one column into
one row.

I have two tables: 'locations' containing a dozen records about
cities and their geographic coodinates - the other table,'testators',
contains several hundred records referring to wills (each will is
given a unique number) made by people in each of the towns in the
locations table.

I would like to query both tables so that a table is produced that
has one row for each of the dozen cities and in each row is the name
of the city, its coordinates and then a column containing all of the
wills (i.e. the unique identifying number of each) from that city.

Is this possible and if so, how?

Thanks!
-- Anthony Masinton

Re: Combining data in different rows?

From
Richard Huxton
Date:
Anthony Masinton wrote:
> I would like to combine data from different rows in one column into one
> row.
>
> I have two tables: 'locations' containing a dozen records about cities
> and their geographic coodinates - the other table,'testators', contains
> several hundred records referring to wills (each will is given a unique
> number) made by people in each of the towns in the locations table.
>
> I would like to query both tables so that a table is produced that has
> one row for each of the dozen cities and in each row is the name of the
> city, its coordinates and then a column containing all of the wills
> (i.e. the unique identifying number of each) from that city.

You'll want a custom aggregate (like sum()). See the link below for an
example of how to accumulate to an array.
http://www.postgresql.org/docs/8.2/static/xaggr.html

--
   Richard Huxton
   Archonet Ltd

Re: Combining data in different rows?

From
Bruno Wolff III
Date:
On Tue, Jan 02, 2007 at 12:33:14 -0700,
  Anthony Masinton <amasinton@gmail.com> wrote:
> I would like to combine data from different rows in one column into
> one row.
>
> I have two tables: 'locations' containing a dozen records about
> cities and their geographic coodinates - the other table,'testators',
> contains several hundred records referring to wills (each will is
> given a unique number) made by people in each of the towns in the
> locations table.
>
> I would like to query both tables so that a table is produced that
> has one row for each of the dozen cities and in each row is the name
> of the city, its coordinates and then a column containing all of the
> wills (i.e. the unique identifying number of each) from that city.
>
> Is this possible and if so, how?

Collapsing rows is done with aggregate functions. You can write a custom
aggregate that collapses the wills for a particular city. How you do this
depends on how you want to store the combined wills (e.g. array, text string).

Re: Combining data in different rows?

From
btober@ct.metrocast.net
Date:
Bruno Wolff III wrote:
> On Tue, Jan 02, 2007 at 12:33:14 -0700,
>   Anthony Masinton <amasinton@gmail.com> wrote:
>
>> I would like to combine data from different rows in one column into
>> one row.
>>
>> ...
>>
>> Is this possible and if so, how?
>>
>
> Collapsing rows is done with aggregate functions. You can write a custom
> aggregate that collapses the wills for a particular city. How you do this
> depends on how you want to store the combined wills (e.g. array, text string).
>

Check out the User Comments section at
"http://www.postgresql.org/docs/8.0/interactive/sql-createaggregate.html"