Comma Separated to Rows

[beulajo]

How to convert comma separated field value into respective rows in query?

I have a field say Type="data1,type1,string1,tom"

This is one field of a table which consists of 5 other fields

My output need to be of 5 rows with all the other fields repeated in all the rows

field1 field2 field3 Type
1 hh 2 data1
1 hh 2 type1
1 hh 2 string1
1 hh 2 tom


This should be my output while fetching the data from the database.


Thanks in advance

[OraMaster]

Did you try anything to achieve this?
By the way pls post your table structure along with sample data.

[debasisdas]

Read this.

[amitpatel66]

Check this:

SQL> ed
Wrote file afiedt.buf
 
  1  create or replace procedure my_Data (p_list IN VARCHAR2,p_ret_data OUT VARCHAR2) IS
  2  plist VARCHAR2(10000) := NULL;
  3  /*TYPE my_dat is RECORD(field1 my_test.field1%type,
  4                        field2 my_test.field2%type,
  5                        field3 my_test.field3%type,
  6                        type my_test.type1%type);*/
  7  TYPE mydat IS TABLE OF my_test%rowtype;
  8  my_rec mydat ;
  9  BEGIN
 10  plist := CHR(39)||REPLACE(p_list,',',CHR(39)||','||CHR(39))||CHR(39);
 11  EXECUTE IMMEDIATE 'SELECT field1,field2,field3,type1 FROM my_Test
 12                     WHERE type1 IN ('||plist||')' bulk collect into my_rec;
 13  FOR I IN my_rec.first..my_rec.last LOOP
 14  p_ret_data := p_ret_Data||my_rec(i).field1||','||my_rec(i).field2||','||
 15                my_rec(i).field3||','||my_rec(i).type1||CHR(10);
 16  end loop;
 17* end;
SQL> /
 
SQL> var my_cur varchar2(4000)
 
SQL> exec my_data('type1,string1',:my_cur);
 
SQL> print :my_cur;
 
MY_CUR
----------------------------
1,hh,2,type1
1,hh,2,string1
 
SQL> exec my_data('type1',:my_cur);
 
PL/SQL procedure successfully completed.
 
SQL> print :my_cur
 
MY_CUR
-----------------------------------------------
1,hh,2,type1
 
SQL>