Re: Comma delimited values into rows

fitzjarrell_at_cox.net wrote:
> johnv wrote:
> > I have this string in a field called 'ID':
> >
> > 1000,1001,1002,...n
> >
> > In sql only, how can i write a view that treats the comma as a new row
> > so that my result set reads as:
> >
> > ID
> > -------
> > 1000
> > 1001
> > 1002
>
> SQL> select id
> 2 from listest;
>
> ID
> --------------------------------------------------------------------------------
> 1000,1001,1002,1003,1004
>
> SQL> select translate(id, ',', chr(10)) from listest;
>
> TRANSLATE(ID,',',CHR(10))
> --------------------------------------------------------------------------------
> 1000
> 1001
> 1002
> 1003
> 1004
>
>
> SQL>
>
>
> David Fitzjarrell

Which, David, is NOT what he wants! His question was

> In sql only, how can i write a view that treats the
> comma as a new row so that my result set reads as:

Note the **treats the comma as a new row**. Your solution doesn't meet this requirement. All your solution does is substitute a comma for a \n, the data is still one row and he still has the same problem! Regards
Barry Received on Thu May 11 2006 - 01:38:35 CDT