Re: Comma delimited values into rows

Mark C. Stock wrote:
> "DA Morgan" <damorgan_at_psoug.org> wrote in message
> news:1147367046.318985_at_bubbleator.drizzle.com...
> : bbulsara23_at_hotmail.com wrote:
> : > 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
> :
> : Then try this:
> :
> : CREATE OR REPLACE TYPE str_array AS TABLE OF VARCHAR2(10);
> : /
> :
> : CREATE OR REPLACE FUNCTION tf (stringin VARCHAR2) RETURN str_array
> : PIPELINED IS
> : i PLS_INTEGER;
> : str VARCHAR2(100);
> : tab sys.dbms_utility.uncl_array;
> : BEGIN
> : str := '"' || REPLACE(stringin, ',', '","') || '"';
> : sys.dbms_utility.comma_to_table(str, i, tab);
> : FOR j IN 1 .. 5 LOOP
> : PIPE ROW(TRANSLATE(tab(j),'A"','A'));
> : END LOOP;
> : RETURN;
> : END;
> : /
> :
> : SELECT *
> : FROM TABLE(CAST(tf('1001,1002,1003,1004,1005')
> : AS str_array));
> : --
> : Daniel A. Morgan
> : University of Washington
> : damorgan_at_x.washington.edu
> : (replace x with u to respond)
> : Puget Sound Oracle Users Group
> : www.psoug.org
>
> "In sql only"
>
> ++ mcs

In SQL only people don't stitch together comma delimited strings. ;-)

-- 
Daniel A. Morgan
University of Washington
damorgan_at_x.washington.edu
(replace x with u to respond)
Puget Sound Oracle Users Group
www.psoug.org