SELECT * FROM tabParent;

PARENT_ID	PARENT_NAME
1	            abc
2	            pqr
3	            xyz

SELECT * FROM tabChild;

CHILD_ID	PARENT_ID	CHILD_NAME
1	        1	          aaa
2	        1	          bbb
3	        1	          ccc
4	        2	          ddd
5	        2	          eee

PARENT_ID	PARENT_NAME           CHILD_NAME
1	            abc                  aaa|bbb|ccc
2	            pqr                  ddd|eee
3	            xyz

CREATE TABLE tabParent
(Parent_id	NUMBER
,Parent_name VARCHAR2(100)
);		 
	
CREATE TABLE tabChild
(Child_id	NUMBER
,Parent_id NUMBER
,Child_Name VARCHAR2(100)
);		

INSERT INTO tabParent VALUES (1,'abc');
INSERT INTO tabParent VALUES (2,'pqr');
INSERT INTO tabParent VALUES (3,'xyz');

INSERT INTO tabChild VALUES (1,1,'aaa');
INSERT INTO tabChild VALUES (2,1,'bbb');
INSERT INTO tabChild VALUES (3,1,'ccc');
INSERT INTO tabChild VALUES (4,2,'ddd');
INSERT INTO tabChild VALUES (5,2,'eee');

SQL> with
  2    children as (
  3      select parent_id,
  4             substr(sys_connect_by_path(child_name,'|'),2) children
  5      from ( select parent_id, child_name,
  6                    row_number() over (partition by parent_id order by child_id) child_rk
  7             from tabchild )
  8      where connect_by_isleaf = 1 and rownum > 0
  9      connect by prior parent_id = parent_id and prior child_rk = child_rk - 1
 10      start with child_rk = 1
 11    )
 12  select p.parent_id, p.parent_name, c.children
 13  from tabparent p, children c
 14  where c.parent_id (+) = p.parent_id
 15  order by p.parent_id
 16  /
 PARENT_ID PARENT_NAME          CHILDREN
---------- -------------------- --------------------
         1 abc                  aaa|bbb|ccc
         2 pqr                  ddd|eee
         3 xyz

SQL> select p.parent_id, p.parent_name,
  2         substr(stragg2(stragg_elem(child_name,'|')),2) children
  3  from tabparent p, tabchild c
  4  where c.parent_id (+) = p.parent_id
  5  group by p.parent_id, p.parent_name
  6  order by p.parent_id
  7  /
 PARENT_ID PARENT_NAME          CHILDREN
---------- -------------------- --------------------
         1 abc                  aaa|ccc|bbb
         2 pqr                  ddd|eee
         3 xyz

SCOTT@orcl > select  p.parent_id,
  2                  parent_name,
  3                  listagg(child_name,'|') within group(order by child_id) children
  4            from  tabParent p,
  5                  tabChild  c
  6            where c.parent_id(+) = p.parent_id
  7            group by p.parent_id,
  8                     parent_name
  9            order by p.parent_id
 10  /

 PARENT_ID PARENT_NAME     CHILDREN
---------- --------------- --------------------
         1 abc             aaa|bbb|ccc
         2 pqr             ddd|eee
         3 xyz

SCOTT@orcl > 

SCOTT@orcl > select  p.parent_id,
  2                  parent_name,
  3                  rtrim(xmlagg(xmlelement(e,child_name,'|').extract('//text()') order by child_id desc),'|') children
  4            from  tabParent p,
  5                  tabChild  c
  6            where c.parent_id(+) = p.parent_id
  7            group by p.parent_id,
  8                     parent_name
  9            order by p.parent_id
 10  /

 PARENT_ID PARENT_NAME     CHILDREN
---------- --------------- --------------------
         1 abc             ccc|bbb|aaa
         2 pqr             eee|ddd
         3 xyz

SCOTT@orcl >