Comma Delimited Value String returned in Separate Records

To simplify this, I am running a query that returns one record with two columns. The first column has the code and the second has a single string with multiple values delimited by commas.  I would like the query to return one record for every comma delimited value in the second column.  

Here is what I currently get:

Code                Values
---------             -----------------
Code1              123,456,789


Here is what I would like to get:

Code                Values
---------             -----------------
Code1              123
Code1              456
Code1              789


I am sure I would need some kind of custom function or stored procedure. Any help would be greatly appreciated.
cortiliusAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Guy Hengel [angelIII / a3]Billing EngineerCommented:
0
sdstuberCommented:
another way


SELECT   code, x.COLUMN_VALUE VALUE
  FROM   yourtable, table(str2tbl(vals)) x

code for str2tbl (or similar functions) can be found on asktom and lots of other sites

here's my version (they are all pretty much the same)
CREATE OR REPLACE TYPE  VCARRAY AS TABLE OF VARCHAR2(4000);
 
CREATE OR REPLACE FUNCTION str2tbl(p_string IN VARCHAR2, p_delimiter IN VARCHAR2 := ',')
        RETURN vcarray PIPELINED
    AS
        v_length   NUMBER := LENGTH(p_string);
        v_start    NUMBER := 1;
        v_index    NUMBER;
    BEGIN
        WHILE(v_start <= v_length)
        LOOP
            v_index    := INSTR(p_string, p_delimiter, v_start);
 
            IF v_index = 0
            THEN
                PIPE ROW(SUBSTR(p_string, v_start));
                v_start    := v_length + 1;
            ELSE
                PIPE ROW(SUBSTR(p_string, v_start, v_index - v_start));
                v_start    := v_index + 1;
            END IF;
        END LOOP;
 
        RETURN;
    END str2tbl;

Open in new window

0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Mark GeerlingsDatabase AdministratorCommented:
If you find (or write) a query or function that returns the data the way you want, you could then construct a view that does this (complex work) for you.  This allows you to just use a very simple query from the view when you need the data.
0
cortiliusAuthor Commented:
That code wroked great.  I created the custom function and type and everything works great.
0
sdstuberCommented:
glad I could help
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Oracle Database

From novice to tech pro — start learning today.