Re: Denormalizing during select

On Tue, 25 Feb 2003 01:22:42 +0000 (UTC), josh(at)agliodbs(dot)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
Donald Duck 60 Quacks
Goofy 45 <null>

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

Thanks,

...Edmund.