Posted by Andy Hassall on 06/15/05 02:57

On Tue, 14 Jun 2005 18:49:57 -0500, "steve" <a@b.com> wrote:

>not getting a response from oracle ngs...hope the post here is not
>objectionable.

Your post appears completely free of PHP content so comp.databases would
probably have been the next step up, where you would likely receive the answer
I'm about to post...

>i've got a table that's basically:
>
>create table foo
>(
> id number
> values varchar2(32767)
>)

Not in Oracle, you haven't. 4000 is the limit for varchar2.

>the values column contains space-seperated data like:
>
>A B C D

This is fundamentally wrong, look up "First Normal Form".

>so the table data 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. any help with this would be much appreciated!

Your schema is wrong. Don't put multiple values in a single field.

Replace the schema with the actual four rows that you post as output.

--
Andy Hassall / <andy@andyh.co.uk> / <http://www.andyh.co.uk>
<http://www.andyhsoftware.co.uk/space> Space: disk usage analysis tool

[Back to original message]