create table temp_name (id  number, sub_id number, txt varchar2(30));

insert into temp_name values(1,1, 'samarjit');
insert into temp_name values(1,2, 'pattnaik');
insert into temp_name values(1,3, 'Mr.');
insert into temp_name values(2,1, 'sachin');
insert into temp_name values(2,2, 'Tendulkar');
insert into temp_name values(2,3, 'Mr.');
insert into temp_name values(3,1, 'Richard');
insert into temp_name values(3,2, 'Hardlee');
insert into temp_name values(3,3, 'Sir.');

select id
      ,max(decode(sub_id,3,txt||' ',null))
      ||max(decode(sub_id,2,txt||' ',null))
      ||max(decode(sub_id,1,txt,null))
from   temp_name
group by id;

"ID"	"NAME"
"1"	"Mr. pattnaik samarjit"
"2"	"Mr. Tendulkar sachin"
"3"	"Sir. Hardlee Richard"