How to Generate a Hierarchy Path in SQL That Leads to a Given Node

How can I generate a hierarchy path in SQL that leads to a given node?

Here is my solution, Sql Fiddle

DECLARE @child VARCHAR(10) = 'C'

    ;WITH children AS
    (

       SELECT 
         ParentCategoryId,
        CAST(ISNULL(ParentCategoryId + '->' ,'')  + ChildCategoryId AS VARCHAR(4000)) AS Path
       FROM Hierarchy
       WHERE ChildCategoryId =  @child
     UNION ALL
       SELECT 
         t.ParentCategoryId,
         list= CAST(ISNULL(t.ParentCategoryId  + '->' ,'')  + d.Path AS VARCHAR(4000))
       FROM Hierarchy t
       INNER JOIN children  AS d
            ON t.ChildCategoryId = d.ParentCategoryId
     )

    SELECT Path 
    from children c
    WHERE ParentCategoryId IS NULL

Output:

A->D->C 
A->B->C 

---

UPDATE:

@AlexeiMalashkevich, to just get id, you may try this

SQL Fiddle

DECLARE @child VARCHAR(10) = 'C'

;WITH children AS
(

   SELECT 
     ParentCategoryId,
     ChildCategoryId  AS Path
   FROM Hierarchy
   WHERE ChildCategoryId =  @child
 UNION ALL
   SELECT 
     t.ParentCategoryId,
     d.ParentCategoryId 
   FROM Hierarchy t
   INNER JOIN children  AS d
        ON t.ChildCategoryId = d.ParentCategoryId
 )

SELECT DISTINCT PATH
from children c

SQL Hierarchy - Resolve full path for all ancestors of a given node

Okay this has been bothering me since I have read the question and I just came back to think of it again..... Anyway, why do you need to recurse back down to get all of the descendants? You have asked for ancestors not descendants and your result set is not trying to get other siblings, grand children, etc.. It is getting a parent and a grand parent in this case. Your First cte gives you everything you need to know except when an ancestor id is also the parentid. So with a union all, a little magic to setup the originating ancestor, and you have everything you need without a second recursion.

declare @test int = 6;

with cte as (

    -- leaf nodes
    select id, parentid, id as terminalid
    from HiearchyTest
    where isleaf = 1

    union all

    -- walk up - preserve "terminal" item for all levels
    select h.id, h.parentid, c.terminalid
    from HiearchyTest as h
    inner join
    cte as c on h.id = c.parentid

)

, cteAncestors AS (

    SELECT DISTINCT
       id = IIF(parentid IS NULL, @Test, id)
       ,parentid = IIF(parentid IS NULL,id,parentid)
    FROM
       cte
    WHERE
       terminalid = @test

    UNION

    SELECT DISTINCT
       id
       ,parentid = id
    FROM
       cte
    WHERE
       terminalid = @test
) 

SELECT
    id = parentid
    ,DecendentId = id
FROM
    cteAncestors
ORDER BY
    id
    ,DecendentId

Your result set from your first cte gives you your 2 ancestors and self related to their ancestor except in the case of the originating ancestors who's parentid is null. That null is a special case I will deal with in a minute.

Remember at this point your query is producing Ancestors not descendants, but what it doesn't give you is self references meaning grandparent = grandparent, parent = parent, self = self. But all you have to do to get that is to add rows for every id and make the parentid equal to their id. hence the union. Now your result set is almost totally shaped up:

The special case of the null parentid. So the null parentid identifies the originating ancestor meaning that ancestor has no other ancestor in your dataset. And here is how you will use that to your advantage. Because you started your initial recursion at the leaf level there is no direct tie to the id that you started with to the originating ancestor, but there is at every other level, simply hijack that null parent id and flip the values around and you now have an ancestor for your leaf.

Then in the end if you want it to be a descendants table switch the columns and you are finished. One last note DISTINCTs are there in case the id is repeated with an additional parentid. E.g. 6 | 3 and another record for 6 | 4

SQL query to get full hierarchy path

Here's a CTE version.

declare @MyTable table (
    NodeId int,
    ParentNodeId int,
    NodeName char(4)
)

insert into @MyTable
    (NodeId, ParentNodeId, NodeName)
    select 1, null, 'Lvl1' union all
    select 2, 1, 'Lvl2' union all
    select 3, 2, 'Lvl3'

declare @MyPath varchar(100)

;with cteLevels as (
    select t.NodeId, t.ParentNodeId, t.NodeName, 1 as level
        from @MyTable t
        where t.ParentNodeId is null
    union all
    select t.NodeId, t.ParentNodeId, t.NodeName, c.level+1 as level
        from @MyTable t
            inner join cteLevels c
                on t.ParentNodeId = c.NodeId
)
select @MyPath = case when @MyPath is null then NodeName else @MyPath + '/' + NodeName end
    from cteLevels
    order by level

select @MyPath

Generate view with tree path by column

Please find below solution for your problem:

with CTE_tree
AS
(
select ht.id_hr, t.id_child, t.id_parent, 1 as rank_
from TREE t
INNER JOIN [THR_TREE] ht ON t.id_child = ht.id_node
UNION ALL
select ct.id_hr, t.id_child, t.id_parent, rank_ + 1 AS rank_
from TREE t
INNER JOIN CTE_tree ct ON ct.id_parent = t.id_child
)
SELECT id_hr, id_child, 'T'+ CAST(ROW_NUMBER() OVER(PARTITION BY id_hr order by rank_ DESC) AS VARCHAR(5)) as rank_
INTO #temp_tree
FROM CTE_tree

select id_hr, [T1], [T2], [T3], [T4], [T5], [T6], [T7], [T8], [T9], [T10]  
FROM (
SELECT id_hr, id_child , rank_ 
FROM #temp_tree )up  
PIVOT  
(
MIN(id_child) FOR rank_ IN ([T1],[T2],[T3],[T4],[T5],[T6],[T7],[T8],[T9],[T10])) AS pvt 
ORDER BY id_hr

Oracle SQL full hierarchy given any node as input

If you want to use it as view you can do something as the following:

WITH rek AS (SELECT item.id
                  , item.name
                  , connect_by_root item.id root_id
               FROM item
              START WITH parent_id IS null
            CONNECT BY NOCYCLE parent_id = PRIOR id)
SELECT startItem.id startId
     , startItem.name startName
     , childItem.id childID
     , childItem.name childName
  FROM rek startItem
  JOIN rek childItem
  USING (root_id)
-- WHERE startItem.id = 3 -- This would be done from outside the view

The subquery rek connects all sibling of the tree with the root element. Then you only have to use this query twice and connect it via the root element to get all elements that are connected via parent-child relation.

If you want to reduce the resultset you can use the SYS_CONNECT_BY_PATH to do so:

WITH rek AS (SELECT item.id
                  , item.name
                  , connect_by_root item.id root_id
                  , SYS_CONNECT_BY_PATH(item.id, '/') path
               FROM item
              START WITH parent_id IS null
            CONNECT BY NOCYCLE parent_id = PRIOR id)
SELECT startItem.id startId
     , startItem.name startName
     , childItem.id childID
     , childItem.name childName
     , childItem.path 
  FROM rek startItem
  JOIN rek childItem
    ON startItem.root_id = childItem.root_id
    AND (startItem.path LIKE childItem.path||'/%'
      OR childItem.path LIKE startItem.path||'/%'
      OR childItem.id = startItem.id)

This for example will give you only the childs and parents of you starting point and no entries from other leafs.

SQL query to get full path from node

First build paths then check permissions

WITH Hierarchy (map_id, name, parent_id, Path)
AS
(
    SELECT 
        t.map_id, t.name, t.parent_id, 
        CAST(t.name AS varchar(max))
    FROM 
        tblMapping t
    LEFT OUTER JOIN 
        tblMapping t1 ON t1.map_id = t.parent_id 
    WHERE 
        t1.map_id IS NULL

    UNION ALL

    SELECT 
        t.map_id, t.name,t.parent_id, 
        CAST(h.Path + '.' + t.name AS varchar(max))
    FROM 
        Hierarchy h
    INNER JOIN 
        tblMapping t ON t.parent_id = h.map_id

)
SELECT Path
FROM Hierarchy h
JOIN tblPerms t2 ON t2.map_id = h.map_id
WHERE Path is not null
OPTION (MAXRECURSION 0);

How to retrieve the path to a node in a tree - efficiently (related to the post 'parse a flat table into a tree?')

SELECT ancestor_id
FROM ClosureTable
WHERE descendant_id = 4;

Returns the values 1, 2, 4. But they are returned on separate rows, and they give no indication that they're in the right order (we might not assume numerical order corresponds to tree hierarchy order).

Sometimes you would also store the depth for every path in the ClosureTable. But even if not, you can count how many ancestors a given node has, and use that to sort:

SELECT ct1.ancestor_id, COUNT(*) AS depth
FROM ClosureTable ct1
 JOIN ClosureTable ct2 ON (ct1.ancestor_id = ct2.descendant_id)
WHERE ct1.descendant_id = 4
GROUP BY ct1.ancestor_id
ORDER BY depth;

Yes, this still returns the result in three rows. If you use MySQL, you have access to GROUP_CONCAT(). Otherwise it's easy to fetch three rows and concatenate their values in application code.

get last node given the full path of all ancestor's node attributes using cte

This should perform very well, as it eliminates impossible paths immediately:

WITH RECURSIVE cte AS (
   SELECT id, parent_id, name
         ,'{holiday,spain,2013}'::text[] AS path  -- provide path as array here
         ,2 AS lvl                                -- next level
   FROM   items
   WHERE  parent_id IS NULL
   AND    name = 'holiday'                        -- being path[1]

   UNION ALL
   SELECT i.id, i.parent_id, i.name
         ,cte.path, cte.lvl + 1
   FROM   cte 
   JOIN   items i ON i.parent_id = cte.id AND i.name = path[lvl]
)
SELECT id, parent_id, name
FROM   cte
ORDER  BY lvl DESC
LIMIT  1;

Assuming you provide a unique path (only 1 result).

->SQLfiddle demo

---

Related Topics