Home » SQL & PL/SQL » SQL & PL/SQL » string aggregation (10g)
string aggregation [message #664676] Thu, 27 July 2017 06:08 Go to next message
praf1981
Messages: 3
Registered: July 2017
Junior Member
I have two tables t1 and t2

t1 has a number column R which contains numbers

structure of t1 is as below:

RNUM R 
1    4 
2    9 
3    2 
4    7 
5    10 
6    5 
7    1 
8    3 
9    8 
10   6 


t2 has a char column R which contains combination of numbers from table t1's R column

e.g:
R1 
2,4,9 
3,6,8 


I want to select only those records from t1 which is there in T2 table
in above example rows having value 2,4,9,3,6,8 should be selected.

Re: string aggregation [message #664677 is a reply to message #664676] Thu, 27 July 2017 06:18 Go to previous messageGo to next message
Michel Cadot
Messages: 68625
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

With any SQL or PL/SQL question, please, Post a working Test case: create table (including all constraints) and insert statements along with the result you want with these data then we will be able work with your table and data. Explain with words and sentences the rules that lead to this result.

Re: string aggregation [message #664713 is a reply to message #664676] Sun, 30 July 2017 23:42 Go to previous message
Barbara Boehmer
Messages: 9077
Registered: November 2002
Location: California, USA
Senior Member
There are a lot of ways to do this. I have provided 2 methods below.

-- test data:
SCOTT@orcl_12.1.0.2.0> select * from t1 order by rnum
  2  /

      RNUM          R
---------- ----------
         1          4
         2          9
         3          2
         4          7
         5         10
         6          5
         7          1
         8          3
         9          8
        10          6

10 rows selected.

SCOTT@orcl_12.1.0.2.0> select * from t2 order by r1
  2  /

R1
------------------------------
2,4,9
3,6,8

2 rows selected.

-- method1:
SCOTT@orcl_12.1.0.2.0> select t1.*
  2  from   t1, t2
  3  where  instr (',' || t2.r1 || ',', ',' || t1.r || ',') > 0
  4  order  by t1.rnum
  5  /

      RNUM          R
---------- ----------
         1          4
         2          9
         3          2
         8          3
         9          8
        10          6

6 rows selected.

-- method2:
SCOTT@orcl_12.1.0.2.0> select t1.*
  2  from   t1,
  3  	    (select regexp_substr (t2.r1, '[^,]+', 1, column_value) r1_num
  4  	     from   t2,
  5  		    table
  6  		      (cast
  7  			 (multiset
  8  			    (select  rownum
  9  			     from    dual
 10  			     connect by level <= regexp_count (t2.r1, ',') + 1)
 11  			  as sys.odcinumberlist))) t3
 12  where  t1.r = t3.r1_num
 13  order  by t1.rnum
 14  /

      RNUM          R
---------- ----------
         1          4
         2          9
         3          2
         8          3
         9          8
        10          6

6 rows selected.
Previous Topic: Convert Columns to Rows
Next Topic: Regular Expression : Find and Replace Specific String
Goto Forum:
  


Current Time: Thu Mar 28 16:26:06 CDT 2024