Posted by Paul on 07/26/07 09:54

Hi Erland,

>The design you aim at is known as EAV, and many frown at it.

Is there a 'better' method for storing this type of data? If so I
would be interested to know about it.

> SELECT per.id, per.dob,
> Home = MIN(CASE ph.Type WHEN 'Home' THEN ph.Value END),
> Mob = MIN(CASE ph.Type WHEN 'Mob' THEN ph.Value END),

This works very well actually, and the execution plan seems very
reasonable compared to previous efforts! There is a problem though, in
that some of the attributes are defined as text columns, which of
course we cannot use the MIN aggregate function on. Do you have any
suggestions for how we can include text columns using similar syntax
and without using sub-selects?

Thanks again,

Paul

[Back to original message]