Обсуждение: Combining data in different rows?
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
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
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).
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"