concaternate connect prior return values

hi i like to concaternate my return value e.g if an object got more than one parent i what to return all parent for that object in one column,i what to avoid duplicate values
i what values to be like this
    Obj_Name       child_name      Parent_name

    MainObject     ThirdObject    (null)

    SecondObject   FourthObject    MainObject

    FourthObject   ThirdObject     SecondObject

    ThirdObject    ThirdObject     SecondObject,MainObject
my sql is
select obj_name, obj_child, obj_parent, 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
   )

Open in new window

chalie001Asked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

flow01Commented:
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
/
0
awking00Information Technology SpecialistCommented:
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?
0
chalie001Author Commented:
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'

Open in new window

)
0
Determine the Perfect Price for Your IT Services

Do you wonder if your IT business is truly profitable or if you should raise your prices? Learn how to calculate your overhead burden with our free interactive tool and use it to determine the right price for your IT services. Download your free eBook now!

flow01Commented:
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
/
0
chalie001Author Commented:
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
0
flow01Commented:
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
0
chalie001Author Commented:
i what to return column Obj_type,description from cal_obj table how will i do that
0
flow01Commented:
one way would be

select
c.obj_name, d.Obj_type, d.description , 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
, cal_obj d
where c.obj_name = p.obj_name
and   d.obj_name = c.obj_name
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
chalie001Author Commented:
thanks its correct
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Oracle Database

From novice to tech pro — start learning today.