Comma Separated to Rows

[beulajo]

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

I have a field say Type="data1,typ e1,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:

[code=oracle]
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%t ype);*/
7 TYPE mydat IS TABLE OF my_test%rowtype ;
8 my_rec mydat ;
9 BEGIN
10 plist := CHR(39)||REPLAC E(p_list,',',CH R(39)||','||CHR (39))||CHR(39);
11 EXECUTE IMMEDIATE 'SELECT field1,field2,f ield3,type1 FROM my_Test
12 WHERE type1 IN ('||plist||')' bulk collect into my_rec;
13 FOR I IN my_rec.first..m y_rec.last LOOP
14 p_ret_data := p_ret_Data||my_ rec(i).field1|| ','||my_rec(i). field2||','||
15 my_rec(i).field 3||','||my_rec( i).type1||CHR(1 0);
16 end loop;
17* end;
SQL> /

SQL> var my_cur varchar2(4000)

SQL> exec my_data('type1, string1',:my_cu r);

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>

[/code]