On Tue, 14 Jun 2005 23:22:00 -0500, "steve" <a@[EMAIL PROTECTED]
> wrote:
>please refrain from lectures about normalization on this post...i have no
>control over the table schema and am simply trying to normalize data
within
>this shoddy flat-file type data table.
>
>i've got a table that's basically:
>
>create table foo
>(
> id number
> values varchar2(32767)
>)
>
>the values column contains space-seperated data like:
>
>A B C D
>
>a row of data in this table would look like:
>
>id values
>____________________
>
>1 A B C D
>
>i need a query that would return:
>
>id values
>____________________
>
>1 A
>1 B
>1 C
>1 D
>
>basically, for each id i need to return a row for each value in the
values
>column.
This isn't impossible, you could do it by doing something like
selecting a cartesian product using a self-join, a function to extract
the nth string, then using a statistical function (e.g. MAX) to wrap
everything up together again. But it would run like a dog.
The basic problem you have is that your data structure is simply
wrong. If you want the data returned fro a query to be separate, that
implies it should be separate in the first place. Unlike carpentry
where it's easier to cut things up than it is to stick them together
again, with data it is simpler to add fragments together when required
than to separate them again.
Google "data normalisation" or "data normalization.
Try:
create table foo
(
id number
,val varchar2(realistic_size)
,primary key (id, val)
);
insert into foo (1, 'A');
insert into foo (1, 'B');
insert into foo (1, 'C');
insert into foo (1, 'D');
select * from foo;
Of course, then your problem might become one of how to stick the data
back together again, but I have a suspicion you don't need to do this.
Lemming
--
Curiosity *may* have killed Schrodinger's cat.
|
