Re: Denormalizing during select

On Tue, 25 Feb 2003 01:22:42 +0000 (UTC), josh@agliodbs.com (Josh
Berkus) wrote:

>Can you post the start of this thread?  I think there's a more
>efficient way using custom aggregates, but somhow I never 
>received the original question.

I'm hoping there is... somehow, using a function to loop over every
row seems a bit inefficient? Here's my original question...

I have two tables (A and B) in a one-to-many relationship. When
retrieving data from one table (A), I need to denormalize the tables
during a select so that values of a column in the many table (B)
appear as a list in one field of the output. E.g.:

create table one_table (   name     varchar(20),   age      integer,   --   constraint one_table_pk       primary key
(name)
);

create table many_table (   name     varchar(20),   property varchar(15),   --   constraint many_table_pk       primary
key(name, property),   constraint many_table_fk       foreign key (name) references one_table (name)
 
);

So one_table could contain:   name            age   ----            ---   Mickey Mouse    50   Donald Duck     60
Goofy          45
 

and many_table could contain:   name            property   ----            --------   Mickey Mouse    Small   Mickey
Mouse   Black   Mickey Mouse    Squeaks   Donald Duck     Quacks
 


The query I'm trying to write would have as output something like:
   name           age   properties   ----           ---   ----------   Mickey Mouse   50    Small, Black, Squeaks
DonaldDuck    60    Quacks   Goofy          45    <null>
 


Could somebody please help point me in the right direction? I imagine
this is a common need...

Thanks,

...Edmund.