In the process of learning SQL I came across a normalized test table
(SQL Server 2005) containing height and weight values like so:

datevalue testid parameterid parametervalue
--------- ------ ----------- --------------
2009-01-01 1 1 163
2009-01-01 1 2 70
2009-01-01 2 1 158
2009-01-01 2 2 77
2009-01-02 1 1 164
2009-01-02 1 2 78
2009-01-02 2 2 55

Parameterid 1 is Height and parmaterid 2 is Weight.
Datevalue/testid/paramterid are all primary keys. I wanted to
display the result as follows:

datevalue testid height weight
--------- ------ ------ ------
2009-01-01 1 163 70
2009-01-01 2 158 77
2009-01-02 1 164 78
2009-01-02 2 NULL 55

The table has over 4 million rows. I tried to write the query below and
it took over 15 seconds:

select distinct(datevalue), testid,
(select parametervalue from testing where testid=a.testid and
datevalue=a.datevalue and parameterid=1) as height,
(select parametervalue from testing where testid=a.testid and
datevalue=a.datevalue and paramterid=2) as weight
from testing a order by datevalue, testid

Could you please share a more efficient query to write on the normalized
table than the one above?

(gempak at (no spam) SAE206-06C1DR.jacks.local) writes:
In the process of learning SQL I came across a normalized test table
(SQL Server 2005) containing height and weight values like so:

datevalue testid parameterid parametervalue
--------- ------ ----------- --------------
2009-01-01 1 1 163
2009-01-01 1 2 70
2009-01-01 2 1 158
2009-01-01 2 2 77
2009-01-02 1 1 164
2009-01-02 1 2 78
2009-01-02 2 2 55

Parameterid 1 is Height and parmaterid 2 is Weight.
Datevalue/testid/paramterid are all primary keys. I wanted to
display the result as follows:

It can be disputed whether this table is normalized, or at least whether
this is a proper design. Height and weight certainly sounds like
different attributes, and thus should be different columns.

So there are
situations where EAV has its place, but usually as the least of all
evils. Using it as a matter of routine is definitely wrong.

datevalue testid height weight
--------- ------ ------ ------
2009-01-01 1 163 70
2009-01-01 2 158 77
2009-01-02 1 164 78
2009-01-02 2 NULL 55


The best way to pivot like you want to do is usually:

SELECT datevalue, testid,
height = MAX(CASE parameterid WHEN 1 THEN parametervalue END),
weight = MAX(CASE parameterid WHEN 2 THEN parametervalue END)
FROM testing
GROUP BY datevalue, testid

The MAX here is only used for aggregating, and does not really have anything
to do with MAX at all.

I see. I had thought that the reason the test table had parameterid was
because it would allow adding more attributes at a later time, such as
length-of-arms, waist, blood-pressure and so on.

In case if height and weight were columns and if other attributes were
to be added at a later time, there could be gaps in dataset. What would
be a good way of designing a table where there are possibilities of
adding more attributes? Would the gaps in dataset be okay if attributes
are added as columns? I believe this is a subjective question but your
expertise and thoughts will greatly help me.

zf (zf at (no spam) jak.local) writes:
[snipped useful message only for brevity]

But then there are situations when users want to be able add new fields
on the fly, and this is a difficult situation where no solution is
really pleasant. I can think of three:

o Lots of user_defined_1, user_defined_2 etc columns.
o Provide a functions for superusers to add columns to the table in
a controlled way.
o The EAV design that you have encountered.

Whichever way you go, you need a way get the application to generate
the queries.