Re: Help w/Normalized VIEW

In article <3493db10.39103204_at_news.mindspring.com>, derman_at_mindspring.com (Jon Derman) wrote:

> If I have a table that looks something like this:
>
> SSN VARCHAR2(9)
> COURSE1 VARCHAR2(6)
> COURSE2 VARCHAR2(6)
> COURSE3 VARCHAR2(6)
>
> ...can I simply create a normalized VIEW of that table that looks
> something like this (with 3 times as many rows as the original)?:
>
> SSN VARCHAR2(9)
> COURSE VARCHAR2(6)
>
> Can someone please show me example SQL for creating such a VIEW?
>
> Thanks.

Two methods:

create view xxx (ssn,course) as
select ssn,course1 from srce_table
UNION
select ssn,course2 from srce_table
UNION
select ssn,course3 from srce_table

create view xxx (ssn,course) as
select ssn,decode(seq,0,course1,1,course2,2,course3) from srce_table, sequence_numbers
where seq between 0 and 2;

(The second method requires a table sequence_numbers(seq number). Such a table is often useful and I always create such a table in my schema)

HTH

-- 
To get random signatures put text files into a folder called ³Random
Signatures² into your Preferences folder.