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 |
|
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 |
|
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: Sat Jun 29 06:39:37 CDT 2024
|