hi how can i use the listagg in the following query something like
select obj_name, obj_child, LISTAGG(obj_parent, ',') WITHIN GROUP (ORDER BY obj_parent) obj_parents
in this query
WITH hierarchical AS (
SELECT obj_parent
, li.obj_name
, li.obj_type
, li.obj_title
, li.DESCRIPTION
, LEVEL AS lvl
, SYS_CONNECT_BY_PATH(li.obj_name, '/')paths
FROM cal_obj li
LEFT JOIN cal_erd_link er ON (li.obj_name = er.obj_child)
START WITH obj_parent IS NULL
CONNECT BY
NOCYCLE PRIOR obj_name = obj_parent
)
, childs_parents AS (
SELECT obj_name
, LEAD(obj_name) OVER(PARTITION BY substr(paths, 1 ,
CASE WHEN INSTR(paths, '/', 1, 2) > 0
THEN INSTR(paths, '/', 1, 2) -1
ELSE LENGTH(paths)
END)ORDER BY paths) AS child_name
, substr(paths,
INSTR(paths, '/', 1, CASE WHEN lvl > 1 THEN lvl-1 END) + 1 ,
INSTR(paths, '/', 1, CASE WHEN lvl > 1 THEN lvl END)
- INSTR(paths, '/', 1, CASE WHEN lvl > 1 THEN lvl-1 END) - 1 ) AS parent_name
, obj_type
, obj_title
, DESCRIPTION
FROM hierarchical
)
SELECT *
FROM childs_parents