concaternate connect prior return values

try
select obj_name, obj_child, LISTAGG(obj_parent, ',') WITHIN GROUP (ORDER BY obj_parent)  obj_parents, child_id, obj_id --,  parent_id
 from
 (
 select c.obj_name,
          oc.obj_name obj_child,
        op.obj_name obj_parent,
        ec.obj_child child_id,
        c.cal_objid obj_id,
        c.obj_type,
        c.obj_title,
        c.description,
        ep.obj_parent parent_id
   from object_list c,
   cal_erd ec,
   object_list oc,
   cal_erd ep ,
   object_list op
   where ec.obj_parent(+) = c.cal_objid  -- if object is no parent still get an empty row
   and   ec.obj_child  = oc.cal_objid(+) -- if no child relation found stil get an empty row to get empty child-data
   and   ep.obj_child(+)  = c.cal_objid  -- if object is not a child still get an empty row
   and   ep.obj_parent = op.cal_objid(+) -- if no parent relation found stil get an empty row to get empty parent-data
   )
group by obj_name, obj_child,  child_id, obj_id
/

Rather than showing your sql, can you post some sample relevant (not extraneous fields) data for the object_list and cal_erd tables and what you expect as results?

hi this is my sample

create table CAL_OBJ(  obj_name    VARCHAR2(100) not null,  obj_type    VARCHAR2(50),  obj_title   VARCHAR2(50),  description VARCHAR2(500),  sub_system  VARCHAR2(100),  status      VARCHAR2(20));alter table CAL_OBJ  add constraint CAL_OBJ_PK primary key (OBJ_NAME)create table CAL_ERD_LINK(  obj_child    VARCHAR2(100) not null,  obj_parent   VARCHAR2(100) not null,  obj_rel_type VARCHAR2(50))alter table CAL_ERD_LINK  add constraint CAL_ERD_LINK primary key (OBJ_CHILD, OBJ_PARENT)    alter table CAL_ERD_LINK  add constraint CAL_CHILD_LINKFK foreign key (OBJ_CHILD)  references CAL_OBJ (OBJ_NAME);alter table CAL_ERD_LINK  add constraint CAL_PARENT_LINK_FK foreign key (OBJ_PARENT)  references CAL_OBJ (OBJ_NAME);


insert into cal_obj (obj_name,
                             obj_type)
                          values('MainObject',
                                'Form');
                                
                                
             insert into cal_obj (obj_name,
                             obj_type)
                          values('SecondObject',
                                'Form');  
                                
                                
             insert into cal_obj (obj_name,
                             obj_type)
                          values('ThirdObject',
                                'Form');
                                
                                
             insert into cal_obj (obj_name,
                             obj_type)
                          values('FourthObject',
                                'Form');                      
                                
                                select * from cal_erd_link
                                
                                select * from cal_obj
                                where obj_name in ('MainObject','SecondObject','ThirdObject')
          
          insert into cal_erd_link values('SecondObject','MainObject',null);
          
           insert into cal_erd_link values('ThirdObject','MainObject',null);
           
            insert into cal_erd_link values('ThirdObject','SecondObject',null);
            
            insert into cal_erd_link values('FourthObject','SecondObject',null);



the query returning value like

OBJ_NAME	OBJ_CHILD	OBJ_PARENT
MainObject	ThirdObject	
MainObject	SecondObject	
ThirdObject	MainObject
SecondObject	ThirdObject	MainObject
SecondObject	FourthObject	MainObject
ThirdObject		        SecondObject
FourthObject		        SecondObject





i what value to be like this


OBJ_NAME	OBJ_CHILD	          OBJ_PARENTS
MainObject	ThirdObject,SecondObject 		
ThirdObject	MainObject,SecondObject	
SecondObject	ThirdObject,FourthObject  MainObject
ThirdObject     			  SecondObject
FourthObject		                  SecondObject
  
my sql is
select obj_name, obj_child, LISTAGG(obj_parent, ',') WITHIN GROUP (ORDER BY obj_parent)  obj_parents
 from
 (
 select c.obj_name,
          oc.obj_name obj_child,
        op.obj_name obj_parent,
        ec.obj_child child_id,
        c.obj_type,
        c.obj_title,
        c.description,
        ep.obj_parent parent_id
   from cal_obj c,
   cal_erd_link ec,
   cal_obj oc,
   cal_erd_link ep ,
   cal_obj op
   where ec.obj_parent(+) = c.obj_name  -- if object is no parent still get an empty row
   and   ec.obj_child  = oc.obj_name(+) -- if no child relation found stil get an empty row to get empty child-data
   and   ep.obj_child(+)  = c.obj_name  -- if object is not a child still get an empty row
   and   ep.obj_parent = op.obj_name(+) -- if no parent relation found stil get an empty row to get empty parent-data
   and c.obj_name in('MainObject','ThirdObject','SecondObject'

Select allOpen in new window

)

select obj_name, LISTAGG(obj_child, ',') WITHIN GROUP (ORDER BY obj_child)  obj_childs, LISTAGG(obj_parent, ',') WITHIN GROUP (ORDER BY obj_parent)  obj_parents
 from
 (
 select c.obj_name,
          oc.obj_name obj_child,
        op.obj_name obj_parent,
        ec.obj_child child_id,
        c.obj_type,
        c.obj_title,
        c.description,
        ep.obj_parent parent_id
   from cal_obj c,
   cal_erd_link ec,
   cal_obj oc,
   cal_erd_link ep ,
   cal_obj op
   where ec.obj_parent(+) = c.obj_name  -- if object is no parent still get an empty row
   and   ec.obj_child  = oc.obj_name(+) -- if no child relation found stil get an empty row to get empty child-data
   and   ep.obj_child(+)  = c.obj_name  -- if object is not a child still get an empty row
   and   ep.obj_parent = op.obj_name(+) -- if no parent relation found stil get an empty row to get empty parent-data
   and c.obj_name in('MainObject','ThirdObject','SecondObject','FourthObject')
)
group by obj_name
/

am geting this values,why am i geting 2 same values as parent
OBJ_NAME      OBJ_CHILDS                  OBJ_PARENTS
FourthObject      SecondObject
MainObject      SecondObject,ThirdObject      
SecondObject      FourthObject,ThirdObject                      MainObject,MainObject
ThirdObject                                    MainObject,SecondObject



i must have this value
OBJ_NAME      OBJ_CHILDS                  OBJ_PARENTS
MainObject      SecondObject                        
SecondObject      FourthObject                                      MainObject
ThirdObject      ThirdObject                                      SecondObject,MainObject

Because there in the original query there are 2 rows for second object with different childs and the same parent

I don't understand your must have values :  you don't need thirdobject as child of main and second object anymore ?  And what is the thirdobject doing as child of thirdobject ?

Ignoring this I focus on eliminating the double values.
Be aware that if in het long run there will be objects with many childs or many parents you will hit limitatations!

2 ways:
1 based on the original query (this will be probably not the best for performance
2 a new query that first gathers the  childs , then the parents and combines them afterwards
with qry1
as
(
 select c.obj_name,
          oc.obj_name obj_child,
        op.obj_name obj_parent,
        ec.obj_child child_id,
        c.obj_type,
        c.obj_title,
        c.description,
        ep.obj_parent parent_id
   from cal_obj c,
   cal_erd_link ec,
   cal_obj oc,
   cal_erd_link ep ,
   cal_obj op
   where ec.obj_parent(+) = c.obj_name  -- if object is no parent still get an empty row
   and   ec.obj_child  = oc.obj_name(+) -- if no child relation found stil get an empty row to get empty child-data
   and   ep.obj_child(+)  = c.obj_name  -- if object is not a child still get an empty row
   and   ep.obj_parent = op.obj_name(+) -- if no parent relation found stil get an empty row to get empty parent-data
   and c.obj_name in('MainObject','ThirdObject','SecondObject','FourthObject')
)
select
--c.obj_name, LISTAGG(obj_child, ',') WITHIN GROUP (ORDER BY obj_child)  obj_childs, LISTAGG(obj_parent, ',') WITHIN GROUP (ORDER BY obj_parent)  obj_parents
c.obj_name, obj_childs, obj_parents
from
(select  obj_name, LISTAGG(obj_child, ',') WITHIN GROUP (ORDER BY obj_child)  obj_childs
 from
 (select distinct obj_name, obj_child
 from qry1
 )
 group by obj_name
 ) c
,
(select obj_name, LISTAGG(obj_parent, ',') WITHIN GROUP (ORDER BY obj_parent)  obj_parents
 from
 (select distinct obj_name, obj_parent
 from qry1
 )
 group by obj_name
 ) p
where c.obj_name = p.obj_name
--group by c.obj_name


select
c.obj_name, obj_childs, obj_parents
from
(select  obj_name, LISTAGG(obj_child, ',') WITHIN GROUP (ORDER BY obj_child)  obj_childs
 from
 (select distinct c.obj_name,
          oc.obj_name obj_child
   from cal_obj c,
   cal_erd_link ec,
   cal_obj oc
   where ec.obj_parent(+) = c.obj_name  -- if object is no parent still get an empty row
   and   ec.obj_child  = oc.obj_name(+) -- if no child relation found stil get an empty row to get empty child-data
   and c.obj_name in('MainObject','ThirdObject','SecondObject','FourthObject')
 )
 group by obj_name
 ) c
,
(select obj_name, LISTAGG(obj_parent, ',') WITHIN GROUP (ORDER BY obj_parent)  obj_parents
 from
 (select distinct c.obj_name,
         op.obj_name obj_parent
   from cal_obj c,
   cal_erd_link ep ,
   cal_obj op
   where ep.obj_child(+)  = c.obj_name  -- if object is not a child still get an empty row
   and   ep.obj_parent = op.obj_name(+) -- if no parent relation found stil get an empty row to get empty parent-data
   and c.obj_name in('MainObject','ThirdObject','SecondObject','FourthObject')
 )
 group by obj_name
 ) p
where c.obj_name = p.obj_name

i what to return column Obj_type,description from cal_obj table how will i do that

thanks its correct