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 Go to next message](/forum/theme/orafaq/images/down.png) |
![](http://www.gravatar.com/avatar/b41fc8d70c04c6ac86dfd4c5e5fe719f?s=64&d=mm&r=g) |
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 message Go to previous message](/forum/theme/orafaq/images/up.png) ![Go to next message Go to next message](/forum/theme/orafaq/images/down.png) |
![](http://www.orafaq.com/forum/images/custom_avatars/43710.gif) |
Barbara Boehmer
Messages: 9097 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.
|
|
|
|
|
|
|
Goto Forum:
Current Time: Fri Jun 28 14:00:23 CDT 2024
|