Display complete parent hierarchy of each row as an additional column from hierarchical table data

I have a hierarchical data in a table with columns ChildId, ParentId. Parents at the root level will have ChildId column value , but ParentId for those type of rows will be NULL, indicating that they are parent level(ROOT level) rows. Other rows will have ChidId column , and ParentId column will be having ChildId column value of some other row (which they consider as parent ). Now I would like to have an SQL query to display for each row , its complete parent hierarchy in one additional column (Hieararchy) separating each parent id with a "/" character. So the SQL query should have 3 columns in it (ChildId, ParentId , Hierarchy)

Would like to know how to create a view for this. The SQL used should be able to run in all databases.

Can anybody help me on this.

I have the table (Table1) like this:


Expected result from the SQL query is :



Take the example of First child Id : A
A->P->Q->Z (A's parent is P, P's parent is Q, Q's parent is Z).
So the hierarchy column value should be like (start with root symbol , seperated by "/") :
/Z/Q/P/A

 

Why not read the documentation ?
It's all very well described on native documentation and also documentation have good basic examples.

For example:

Code (Text):

WITH tab(CHILD,PARENT) AS
(
SELECT 'A','P' FROM dual UNION ALL
SELECT 'P','Q' FROM dual UNION ALL
SELECT 'Q','Z' FROM dual UNION ALL
SELECT 'B','P' FROM dual UNION ALL
SELECT 'C','F' FROM dual UNION ALL
SELECT 'F','M' FROM dual UNION ALL
SELECT 'M','Q' FROM dual UNION ALL
SELECT 'D','H' FROM dual UNION ALL
SELECT 'H','Y' FROM dual UNION ALL
SELECT 'Y',NULL FROM dual UNION ALL
SELECT 'Z',NULL FROM dual
)

SELECT
       t.*,
       sys_connect_by_path(t.child,'/') p
FROM tab t
START WITH t.parent IS NULL
CONNECT BY PRIOR t.child = t.parent;