Example of Self Join

We have a table with values as:

STID STNAME
==== ======
1         Raj
1         Kumar
2         Rajesh
2         Singh
3         Dinesh
3         Lal
4         Mohit
4        Sharma

We require the output like,

STID STNAME
==== ======
1        Raj Kumar
2        Rajesh Singh
3        Dinesh Lal
4       Mohit Sharma

Solution:
=========

Create Table ST_TEST (STID Number, STNAME Varchar2(10));

insert into ST_TEST values (1, 'Raj');
insert into ST_TEST values (1, 'Kumar');
insert into ST_TEST values (2, 'Rajesh');
insert into ST_TEST values (2, 'Singh');
insert into ST_TEST values (3, 'Dinesh');
insert into ST_TEST values (3, 'Lal');
insert into ST_TEST values (4, 'Mohit');
insert into ST_TEST values (4, 'Sharma');
commit;

Select a.STID, a.STNAME||' '||b.STNAME STNAME 
From ST_TEST a, Test2 b 
Where a.STID=b.STID 
And a.rowid<b.rowid;

Enjoy.