From: | Josh Berkus <josh(at)agliodbs(dot)com> |
---|---|
To: | Daniel Staal <DStaal(at)usa(dot)net>, "pgsql-novice(at)postgresql(dot)org" <pgsql-novice(at)postgresql(dot)org> |
Subject: | Re: scaleable design for multiple value tuple |
Date: | 2003-10-18 02:55:55 |
Message-ID: | 200310171955.55120.josh@agliodbs.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-novice |
Daniel,
> > in the report output i do not want to see three records for the
> > above case (one for each engineer). i just want to see a view of
> > the case but including which engineers belong to that case.
>
> Shouldn't be too hard, in most cases ;-). I'd first try doing it on
> the client side, but then I'm more of a perl programmer than a SQL
> programmer. There is probably a good way to do this in SQL
> (subquerys maybe? Or would you have to use the string concatenation
> operator? Hmmm...), but I'd have to test it and I don't have the
> gumption to come up with good test data at the moment. Keep
> prodding, and you'll probably get someone to come up with something.
He can use custom aggregates to concatenate the 3 engineers, e.g.:
SELECT case_id, case_name, comma_cat(engineer)
FROM cases JOIN case_engineers USING (case_id)
GROUP BY case_id, case_name;
where comma_cat is a concatenation aggregate per the example in CREATE
AGGREGATE.
--
Josh Berkus
Aglio Database Solutions
San Francisco
From | Date | Subject | |
---|---|---|---|
Next Message | Nabil Sayegh | 2003-10-18 13:27:23 | Re: Combining text fields |
Previous Message | Daniel Staal | 2003-10-18 01:38:21 | Re: scaleable design for multiple value tuple |