The SYS_CONNECT_BY_PATH function returns the branch of the node in the hierarchy. It returns a string that represents the concatenation of all the values obtained by evaluating the scalar expression for all the parents of a row, including that row, separated by the separator character, ordered ascending by level.
This function may be used in the SELECT list, WHERE clause and ORDER BY clause.
SYS_CONNECT_BY_PATH (column_name, separator_char)
The following example shows how to execute a hierarchical query with SYS_CONNECT_BY_PATH function.
-- Executing a hierarchical query with SYS_CONNECT_BY_PATH function
SELECT id, mgrid, name, SYS_CONNECT_BY_PATH(name,'/') as [hierarchy]
FROM tree
START WITH mgrid IS NULL
CONNECT BY PRIOR id=mgrid
ORDER BY id;
id mgrid name hierarchy
=================================================
1 null Kim /Kim
2 null Moy /Moy
3 1 Jonas /Kim/Jonas
4 1 Smith /Kim/Smith
5 2 Verma /Moy/Verma
6 2 Foster /Moy/Foster
7 6 Brown /Moy/Foster/Brown