Re: Is there an SQL to create a view that will normalize a denoramlized table?

From: Iain Shanks <shanksi_at_logica.com>
Date: Wed, 04 Aug 1999 08:34:24 +0100
Message-ID: <37A7ED00.848BFAAB@logica.com>

Paul Q wrote:

> create view xxx as
> select id,
> date,
> hour1
> from table
> union
> select id,
> date,
> hour2
> from table
> .
> .
> .
>

What seems to be missing from all the replies so far is how to tell which hour is which in the normalised view. Try the following:

create view myView as
select id,

           date,
           1            hour,
            hour1    reading
       from table

union
select id,

           date,
           2            hour,
            hour2    reading

  from table

    .
    .
    .

This will allow you to do the following sort of thing (which I suspect is what you're after):
select reading
from myView
where id = xxxx
 and date = dd/mm/yyyy
 and hour = nn;

Hope this helps

Iain

---

All opinions are mine and not necessarily shared by Logica

---

Received on Wed Aug 04 1999 - 02:34:24 CDT