1. Get rid of all advertisements and get unlimited access to documents by upgrading to Premium Membership. Upgrade to Premium Now and also get a Premium Badge!

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

Discussion in 'SQL PL/SQL' started by sksubin, Apr 27, 2017.

Tags:
  1. sksubin

    sksubin Starter

    Messages:
    1
    Likes Received:
    0
    Trophy Points:
    10
    Location:
    India
    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:
    upload_2017-4-27_14-55-54.png

    Expected result from the SQL query is :

    upload_2017-4-27_14-57-21.png

    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
     
    Last edited: Apr 27, 2017
  2. krasnoslobodtsev_si

    krasnoslobodtsev_si Forum Genius

    Messages:
    749
    Likes Received:
    145
    Trophy Points:
    830
    Location:
    Russian Federation
    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;