Posted by Erland Sommarskog on 07/26/07 21:19

Paul (paulwragg2323@hotmail.com) writes:
>>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.

Without knowledge about what your tables really contains, I don't want to
say much more of what is good or bad. I just wanted to mention that
the deisgn you are looking has a bad name.

>> 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?

Still on SQL 2000? In SQL 2005, you could use varchar(MAX) which
should work with MIN.

One alternative is to do a lot of joins:

SELECT per.id, per.dbo, Home = home.Value, Mob = Mob.value
FROM persons per
LEFT JOIN phones Home ON per.person = Home.person
AND Home.type = 'Home'
LEFT JOIN phones Mob ON per.person = Mob.person
AND Mob.type = 'Mob'


--
Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx

[Back to original message]