Home » SQL & PL/SQL » SQL & PL/SQL » Get Comma separate Values (Oracle 11gR2)
Get Comma separate Values [message #569064] Fri, 19 October 2012 05:36 Go to next message
srinivas.k2005
Messages: 299
Registered: August 2006
Senior Member
Hi,

I have one scenario here.


Create table a ( Objectid number, Value varchar2(2000);
/

Insert into a values (12, '2,3,4');
Insert into a values (13, '8,7,4');
Insert into a values (14, '3,8,9');
Insert into a values (15, '6,3,11');



I should get the output as:

ID Value
------ ------
12 2
12 3
12 4
13 8
13 7
13 4
14 3
14 8
14 9
15 6
15 3
15 11


Regards,
SRK
Re: Get Comma separate Values [message #569065 is a reply to message #569064] Fri, 19 October 2012 06:04 Go to previous messageGo to next message
muralikri
Messages: 638
Registered: August 2011
Location: chennai
Senior Member

Try like
SELECT   distinct objectid,  REGEXP_SUBSTR (value, '[^,]+', 1, LEVEL) pivot_char
      FROM a  CONNECT BY REGEXP_SUBSTR (value, '[^,]+', 1, LEVEL) IS NOT NULL  
Re: Get Comma separate Values [message #569067 is a reply to message #569064] Fri, 19 October 2012 06:08 Go to previous messageGo to next message
Michel Cadot
Messages: 59166
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
See http://www.orafaq.com/forum/m/569066/102589/#msg_569066

Regards
Michel
Re: Get Comma separate Values [message #569070 is a reply to message #569065] Fri, 19 October 2012 06:18 Go to previous messageGo to next message
Michel Cadot
Messages: 59166
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
@muralikri

If you need DISTINCT then the query is wrong.
See the link I posted.

Regards
Michel
Re: Get Comma separate Values [message #569072 is a reply to message #569070] Fri, 19 October 2012 06:30 Go to previous messageGo to next message
muralikri
Messages: 638
Registered: August 2011
Location: chennai
Senior Member

Yes micheal... Thanks for correction.. Do you any idea to correct my code..
and also i noticed your link it's working fine.
Re: Get Comma separate Values [message #569075 is a reply to message #569072] Fri, 19 October 2012 07:11 Go to previous messageGo to next message
Michel Cadot
Messages: 59166
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
You have to do it joining with a row generator (like in the link), you cannot directly use the CONNECT BY when they are several rows in the table.

Regards
Michel
Re: Get Comma separate Values [message #569080 is a reply to message #569075] Fri, 19 October 2012 07:29 Go to previous message
muralikri
Messages: 638
Registered: August 2011
Location: chennai
Senior Member

Thanks lot...
Previous Topic: Conversion Troubling
Next Topic: ORA-01010 Invalid OCI Operation during SCHEDULER job procedure that selects from db link
Goto Forum:
  


Current Time: Mon Sep 22 16:10:57 CDT 2014

Total time taken to generate the page: 0.11926 seconds