Hierarchical qyery help

Hi All,

I need your help for the below requirement.

The below data is stored in hierarcy table in a tree structure, I am writing a sql as below to get all the parents of a given node,instead of geeting the above four nodes in different records i want to get them ina single record as shown above for a given node which is retrieved by another table

Account:State:District:Region

The Sql which i have written is as below

Note : i have included the brief description of the tables beside the table names


SELECT sa.node_key AS node_key
,((select name from hierarchy where tier=2
start with node_key=ch.node_key connect by prior parent_node_key=node_key
)
||' : '||
(select name from hierarchy where tier=3
start with node_key=ch.node_key connect by prior parent_node_key=node_key
)
||' : '||
(select name from hierarchy where tier=4
start with node_key= ch.node_key connect by prior parent_node_key=node_key
)
||' : '||
ch.name
) AS grouplabel
FROM course c,-- it contains all the course information
student_course sc, -- it contains all the students enrollements into courses
admin sa,-- it contains the admin and sub domains(groups) mapping
student sr,-- it contains the students info
hierarchy ch -- it contains nodes information ina tree structure
WHERE sa.admin_key = 'test13'
AND sa.node_key = ch.node_key
AND ch.node_key = sc.node_key
AND sa.node_key = sc.node_key
AND sr.student_key =sc.student_key
AND c.course_key = sc.course_key
AND sc.date_key >= TO_DATE ('01-Feb-2003')
AND sc.date_key <= TO_DATE ('15-Feb-2003')
AND c.course_name LIKE '%'


Thanks in Advance,

Regds,
Srinivas

Try this.

select rpad('*',2*level,'*')||ename EmpName,
sys_connect_by_path( ename, '/' ) cbp
from emp
start with mgr is null
connect by prior empno = mgr
order SIBLINGS by ename
/