This is not a Postgres specific question.

Given two simple tables tracking the marriages between
people:



The simple query:


will produce multiple rows per person.name, if that particular
person has been married multiple times, such as:

WHAT I WOULD LIKE TO DO, is to collapse the rows
of multiple people into one row and have a query result
that looks like this:

no matter how many marriages any one person
may have had.


Can such a query be written entirely with SQL?
Or is there a common stored procedure that people
use?

Thanks!

On Tue, 22 Dec 2009 10:55:31 -0800, Ivan K. wrote:

I had a similar question the other day and Laurenz suggested using the
unnest function.



--
http://mgogala.byethost5.com

I have an answer to my question. Evidently,
the "create aggregate" statement can help me.
First, I create this:


And then, this query will do what I want:

The only problem remaining, is that
I would like the marriage dates to be in order.
The following query does that, but it is
_very_ slow. Does anybody have a suggestion?

Thanks!

CREATE AGGREGATE textcat_all(
basetype = text,
sfunc = textcat,
stype = text,
initcond = ''
);



And then, this query will do what I want:


select
person.person_id,
person.name,
textcat_all( marriage.marriage_id || '; ') as marriage_ids,
textcat_all( marriage.marriagedate || '; ') as marriage_dates
from
person
left join marriageon
(person.person_id = marriage.husband_id or person.person_id =
marriage.wife_id)
group by
person.person_id,
person.birthdate
order by
person.name;


The only problem remaining, is that
I would like the marriage dates to be in order.
The following query does that, but it is
_very_ slow. Does anybody have a suggestion?


select
person.person_id,
person.name,
textcat_all( blah_blah.marriage_id || '; ') as marridage_ids,
textcat_all( blah_blah.marriagedate || '; ') as marriage_dates
from
person
left join
(
select
marriage.marriage_id,
marriage.marriage_date,
marriage.husband_id,
marriage.wife_id
from marriage
order by marriage.marriage_date
)
blah_blah on
(person.person_id = blah_blah.husband_id or person.person_id =
blah_blah.wife_id)
group by
person.person_id,
person.birthdate
order by
person.name;



Thanks!

I have an answer to my question. Evidently,
the "create aggregate" statement can help me.
First, I create this:


CREATE AGGREGATE textcat_all(
basetype = text,
sfunc = textcat,
stype = text,
initcond = ''
);



And then, this query will do what I want:


select
person.person_id,
person.name,
textcat_all( marriage.marriage_id || '; ') as marriage_ids,
textcat_all( marriage.marriagedate || '; ') as marriage_dates
from
person
left join marriageon
(person.person_id = marriage.husband_id or person.person_id =
marriage.wife_id)
group by
person.person_id,
person.birthdate
order by
person.name;


The only problem remaining, is that
I would like the marriage dates to be in order.
The following query does that, but it is
_very_ slow. Does anybody have a suggestion?


select
person.person_id,
person.name,
textcat_all( blah_blah.marriage_id || '; ') as marridage_ids,
textcat_all( blah_blah.marriagedate || '; ') as marriage_dates
from
person
left join
(
select
marriage.marriage_id,
marriage.marriage_date,
marriage.husband_id,
marriage.wife_id
from marriage
order by marriage.marriage_date
)
blah_blah on
(person.person_id = blah_blah.husband_id or person.person_id =
blah_blah.wife_id)
group by
person.person_id,
person.birthdate
order by
person.name;



Thanks!

Ivan K. wrote:

This is just a guess so your mileage may vary. The order by marriage
date does not help the join on person id. I would try prefixing the
order with the person id, but the twist is you want to join on 2
different person ids. I suggest trying something along the lines of:

person
left join (
select
husband_id as person_id
, marriage_date
, marriage_id
from marriage
union
select
wife_id as person_id
, marriage_date
, marriage_id
from marriage
order by person_id, marriage_date
) holy_union
on person.person_id = holy_union.person_id

--
is there something in it for them, like maybe bailouts, if they can
panic us into doing something politically to cover them?

November 19, 2007 - John S Bolton

http://tinyurl.com/y9e4vxh

On Dec 22, 7:02*pm, Bob Badour <bbad... (AT) pei (DOT) sympatico.ca> wrote:
That is an outstanding solution.
It sorts the marriages by date and is
_fast_. Your "holy_union" humor is
much appreciated too.

I need to learn more about the union
operator.

Thanks!