Home » SQL & PL/SQL » SQL & PL/SQL » Converting comma separated value in different rows (Oracle 10g R2)
Converting comma separated value in different rows [message #661795] Sun, 02 April 2017 22:53 Go to next message
amarbose
Messages: 21
Registered: May 2011
Junior Member
I have some value in a table. These are records of a table in multi row format.

12, 14, 17,18,32
14, 16, 19,22
18,22,23,32,35,37,38
31,35,36,39,41


Output will be all distinct values in different rows:

12
14
16
17
18
19
22
23
31
32
35
36
37
38
39
41
Re: Converting comma separated value in different rows [message #661797 is a reply to message #661795] Sun, 02 April 2017 23:18 Go to previous messageGo to next message
Barbara Boehmer
Messages: 8893
Registered: November 2002
Location: California, USA
Senior Member
SCOTT@orcl_12.1.0.2.0> select some_values from a_table
  2  /

SOME_VALUES
--------------------------------------------------------------------------------
12, 14, 17,18,32
14, 15, 19,22
18,22,23,32,35,37,38
31,35,36,39,41

4 rows selected.

SCOTT@orcl_12.1.0.2.0> select distinct(trim(regexp_substr(some_values, '[^,]+', 1, column_value))) some_value
  2  from   a_table,
  3  	    table
  4  	      (cast
  5  		(multiset
  6  		   (select rownum
  7  		    from   dual
  8  		    connect by level <= regexp_count(some_values,',')+1)
  9  		 as sys.odcivarchar2list))
 10  order  by some_value
 11  /

SOME_VALUE
--------------------------------------------------------------------------------
12
14
15
17
18
19
22
23
31
32
35
36
37
38
39
41

16 rows selected.
Re: Converting comma separated value in different rows [message #661798 is a reply to message #661797] Sun, 02 April 2017 23:24 Go to previous messageGo to next message
amarbose
Messages: 21
Registered: May 2011
Junior Member
Thanks. My Database is 10gR2 and it is not working in that database.
Re: Converting comma separated value in different rows [message #661799 is a reply to message #661798] Mon, 03 April 2017 00:12 Go to previous messageGo to next message
Barbara Boehmer
Messages: 8893
Registered: November 2002
Location: California, USA
Senior Member
I think regexp_count was not available in your version. Try this instead.

SCOTT@orcl_12.1.0.2.0> select distinct(trim(regexp_substr(some_values, '[^,]+', 1, column_value))) some_value
  2  from   a_table,
  3  	    table
  4  	      (cast
  5  		(multiset
  6  		   (select rownum
  7  		    from   dual
  8  		    connect by level <= length(some_values) - length(replace(some_values,','))+1)
  9  		 as sys.odcivarchar2list))
 10  order  by some_value
 11  /

SOME_VALUE
--------------------------------------------------------------------------------
12
14
15
17
18
19
22
23
31
32
35
36
37
38
39
41

16 rows selected.
Re: Converting comma separated value in different rows [message #661800 is a reply to message #661795] Mon, 03 April 2017 00:36 Go to previous messageGo to next message
Michel Cadot
Messages: 65848
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Have a look at row generator.
Read this post and the two subsequent ones.


Re: Converting comma separated value in different rows [message #661802 is a reply to message #661798] Mon, 03 April 2017 01:34 Go to previous message
Michel Cadot
Messages: 65848
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

And feedback in your previous topics maybe you then realized your current question was answered in your previous topic.

Previous Topic: Can we replace any sub query using join
Next Topic: Need help with SQL
Goto Forum:
  


Current Time: Sat Oct 20 23:42:07 CDT 2018