Cte to Traverse Back Up a Hierarchy

CTE to traverse back up a hierarchy?

Ah, I figured it out:

WITH Hierarchy(TaskID, [Subject], ParentID, IsProject, HLevel)
AS
(
    SELECT 
        TaskID, 
        [Subject], 
        ParentID ,
        IsProject,
        0 as HLevel
    FROM 
        Task 
    WHERE 
        TaskID = 59

    UNION ALL

    SELECT 
        SubDepartment.TaskID, 
        SubDepartment.[Subject], 
        SubDepartment.ParentID ,
        SubDepartment.IsProject,
        HLevel + 1
    FROM 
        Task SubDepartment
    INNER JOIN 
        Hierarchy ParentDepartment
    ON 
        SubDepartment.TaskID = ParentDepartment.ParentID 
)

SELECT 
    TaskID, 
    [Subject], 
    ParentID,
    IsProject,
    HLevel
FROM  
    Hierarchy
ORDER BY
    HLevel DESC

CTE Recursion to get tree hierarchy

Try this:

;WITH items AS (
    SELECT EstimateItemID, ItemType
    , 0 AS Level
    , CAST(EstimateItemID AS VARCHAR(255)) AS Path
    FROM EstimateItem 
    WHERE ParentEstimateItemID IS NULL AND EstimateID = @EstimateID

    UNION ALL

    SELECT i.EstimateItemID, i.ItemType
    , Level + 1
    , CAST(Path + '.' + CAST(i.EstimateItemID AS VARCHAR(255)) AS VARCHAR(255))
    FROM EstimateItem i
    INNER JOIN items itms ON itms.EstimateItemID = i.ParentEstimateItemID
)

SELECT * FROM items ORDER BY Path

With Path - rows a sorted by parents nodes

If you want sort childnodes by ItemType for each level, than you can play with Level and SUBSTRING of Pathcolumn....

Here SQLFiddle with sample of data

How to generate Tree path using traverse CTE

Use a recursive CTE to solve this:

WITH recCTE
AS (
    SELECT id,
        parentid,
        id AS original_id,
        parentid AS original_parentid,
        name as original_name,
        1 AS depth,
        CAST(name AS VARCHAR(5000)) AS path
    FROM yourtable

    UNION ALL

    SELECT yourtable.id,
        yourtable.parentid, 
        recCTE.original_id,
        recCTE.original_parentID,
        recCTE.original_name,
        recCTE.depth + 1,
        CAST(recCTE.path + '-' + yourtable.name as VARCHAR(5000))
    FROM recCTE
    INNER JOIN yourtable
        ON recCTE.parentid = yourtable.id
    WHERE depth < 20 /*prevent cycling*/
    )
SELECT original_id as id, original_parentid as parentid, original_name as name, depth, path 
FROM recCTE t1
WHERE depth = (SELECT max(depth) FROM recCTE WHERE t1.original_id = recCTE.original_id)

sqlfiddle example

That CTE has two parts:

The "Anchor Member" which is the first selection from the table. This defines the output (which columns and column type are in the output).
The "Recursive Member" which selects from the CTE in which it's contained at is performed iteratively until the join fails.

In this example we capture the path by concatenating the path to the name over and over again in the recursive member. We also track Depth (how many recursions have been performed) and track the current id and parentid as well as the original id and original parentid so they can be selected in the final SELECT statement.

How to traverse a hierarchical tree-structure structure backwards using recursive queries

UPDATE 2:
I rewrote the original recursive query so that all accumulation/aggregation is done outside the recursive part. It should perform better than the previous version of this answer.
This is very much alike the answer from @a_horse_with_no_name for a similar question.

  WITH 
    RECURSIVE search_graph(edge, from_node, to_node, length, area, start_node) AS
    (
        SELECT edge, from_node, to_node, length, area, from_node AS "start_node"
        FROM tree
        UNION ALL
        SELECT o.edge, o.from_node, o.to_node, o.length, o.area, p.start_node
        FROM tree o
    JOIN search_graph p ON p.from_node = o.to_node
    )
    SELECT array_agg(edge) AS "edges"
       -- ,array_agg(from_node) AS "nodes"
          ,count(edge) AS "edge_count"
          ,sum(length) AS "length_sum"
          ,sum(area) AS "area_sum"
    FROM search_graph
    GROUP BY start_node
    ORDER BY start_node
;

Results are as expected:

 start_node | edges       | edge_count | length_sum |  area_sum
------------+-------------+------------+------------+------------
  1         | {A}         |          1 |        1.1 |       0.9
  2         | {B}         |          1 |        1.2 |       1.3
  3         | {C}         |          1 |        1.8 |       2.4
  4         | {D,B,A}     |          3 |        3.5 |       3.5
  5         | {E,D,C,B,A} |          5 |        6.4 |       6.8

Using Common Table Expression for a recursive bottom up query

For getting a single file's path you will use a recursive CTE of the form:
with folderPath as ( select Id, ParentFolderId, Name from FolderInfo where Id = @FileFolderId union all select p.Id, p.ParentFolderId, p.Name from folderPath p join FolderInfo fi on fi.Id = p.ParentFolderId ) select @FullFolderPath += '/' +Name from folderPath
where @FileFolderId is the Id value for the file you want the path for, and @FullFolderPath is the varchar(8000) variable to aggregate the path.

This will provide you with a good start, but it might need some tweaking.

How to write a CTE to aggregate hierarchical values

It turns out that a very similar question and solution was posted here:

How can I traverse a tree bottom-up to calculate a (weighted) average of node values in PostgreSQL?

Since sqlite3 doesn't let you create functions, the example using a recursive CTE applies:

with recursive cte(id, parent, value, level, total) as (
    select
        t.id, t.parent, t.value,
        0,
        t.value as total
    from test1 t
    where not exists (
        select id
        from test1
        where parent = t.id)
union all
    select
        t.id, t.parent, t.value,
        c.level+1,
        case when t.value is null then c.total else t.value end
    from test1 t
    join cte c on t.id=c.parent
)
select id, parent, value, avg(total) total from (
    select
        id, parent, value, level, avg(total) total
        from cte
        group by id,parent,level
)
group by id, parent
order by id

SQL server - traverse tree in two directions

You can perform a UNION on 2 CTE result set that work both up and down the tree.

So the join in CTE1 would be:

INNER JOIN RegionTree rt ON rt.Id = r.ParentId

And the join on CTE2 would be the other way round to work in reverse:

INNER JOIN RegionTree2 rt ON rt.ParentId = r.Id

So performing a UNION on the CTE results, (sorry it's untested)

WITH RegionTree AS (
    SELECT topRegion.Id RootId, topRegion.Name, topRegion.Id
    FROM Region topRegion
    UNION ALL
    SELECT rt.RootId, r.Name, r.Id
    FROM Region r
    INNER JOIN RegionTree rt ON rt.Id = r.ParentId
),
RegionTree2 AS (
    SELECT topRegion.Id RootId, topRegion.Name, topRegion.Id
    FROM Region topRegion
    UNION ALL
    SELECT rt.RootId, r.Name, r.Id
    FROM Region r
    INNER JOIN RegionTree2 rt ON rt.ParentId = r.Id
)
SELECT rt.RootId, rt.Id, rt.Name, rt.CustomerId, rt.EntityId, rt.Depth
FROM RegionTree rt
WHERE rt.RootId = 2
UNION
SELECT rt.RootId, rt.Id, rt.Name, rt.CustomerId, rt.EntityId, rt.Depth
FROM RegionTree2 rt
WHERE rt.RootId = 2

Looking at the query though, I would possibly modify the CTE's to include the filter condition rather than how it is currently in the WHERE clause, to prevent querying more data than is required. Something like this:

DECLARE @ID INT = 2

WITH RegionTree AS (
    SELECT topRegion.Id RootId, topRegion.Name, topRegion.Id
    FROM Region topRegion
    WHERE topRegion.Id = @ID  --ADDED
    UNION ALL
    SELECT rt.RootId, r.Name, r.Id
    FROM Region r
    INNER JOIN RegionTree rt ON rt.Id = r.ParentId
    WHERE topRegion.Id IS NOT NULL -- ADDED
),
RegionTree2 AS (
    SELECT topRegion.Id RootId, topRegion.Name, topRegion.Id
    FROM Region topRegion
    WHERE topRegion.Id = @ID  --ADDED
    UNION ALL
    SELECT rt.RootId, r.Name, r.Id
    FROM Region r
    INNER JOIN RegionTree2 rt ON rt.ParentId = r.Id
    WHERE topRegion.Id IS NOT NULL -- ADDED
)
SELECT rt.RootId, rt.Id, rt.Name, rt.CustomerId, rt.EntityId, rt.Depth
FROM RegionTree rt
UNION
SELECT rt.RootId, rt.Id, rt.Name, rt.CustomerId, rt.EntityId, rt.Depth
FROM RegionTree2 rt

CTE Recursive Query to Grandparents

You need to add the same isdeleted = 0 AND isinedit = 0 predicate to the INNER JOIN childParent CP source.

...but if you do that you make your CTE query very fiddly and if you have to repeat the same thing over-and-over there's probably a better way to do it.

...and there is! A SELECT query can have multiple CTE expressions:

;
WITH filtered AS
(
    SELECT
        a.id,
        a.name,
        a.parent_id,
    FROM
        dbo.Table
    WHERE
        IsDeleted = 0
        AND
        IsInEdit = 0 
)

WITH cte AS
(
    SELECT
        a.id,
        a.name,
        a.parent_id
    FROM
        filtered

    UNION ALL

    SELECT
        a.id,
        a.name,
        a.parent_id
    FROM
        filtered
        INNER JOIN cte ON a.parent_id = cte.id
)
SELECT  
    *
FROM
    cte
ORDER BY
    id

traversing recursive CTE to the root in SQL Server reaches maximum recursion

You've managed to create an infinite loop. You can stick in a filter against level to debug these:

(also after removing the manager id)

WITH EmpCTE
AS
( 
  -- Anchor Member (AM)
  SELECT 
      empid, 
      empname, 
      mgrid, 
      0 AS level                            -- <------------------- SET LVL START FROM 0
  FROM Employees
  WHERE EMPID = 7
  UNION ALL
  -- Recursive Member (RM)
  SELECT 
      e.empid, 
      e.empname, 
      e.mgrid, 
      e.level+1                     -- <------------------- INCREMENT LVL
  FROM Employees AS m
    JOIN EmpCTE AS e                    -- <------------------- RECURSIVELY CALL EmpCTE
      ON e.mgrid = m.empid
where level < 2
)
SELECT * FROM EmpCTE;

empid       empname                   mgrid       level
----------- ------------------------- ----------- -----------
7           Robert                    3           0
7           Robert                    3           1
7           Robert                    3           2

This is because you are projecting the columns from EmpCTE as e rather than Employees as m, so you're just getting the same data again and again (plus the level being increased).

WITH EmpCTE
AS
( 
  -- Anchor Member (AM)
  SELECT 
      empid, 
      empname, 
      mgrid, 
      0 AS level                            -- <------------------- SET LVL START FROM 0
  FROM Employees
  WHERE EMPID = 7
  UNION ALL
  -- Recursive Member (RM)
  SELECT 
      m.empid,   -- these columns need to come from m
      m.empname, -- these columns need to come from m
      m.mgrid,   -- these columns need to come from m
      e.level+1                     -- <------------------- INCREMENT LVL
  FROM Employees AS m
    JOIN EmpCTE AS e                    -- <------------------- RECURSIVELY CALL EmpCTE
      ON e.mgrid = m.empid
)
SELECT * FROM EmpCTE;

empid       empname                   mgrid       level
----------- ------------------------- ----------- -----------
7           Robert                    3           0
3           Janet                     1           1
1           Nancy                     NULL        2

Cte to Traverse Back Up a Hierarchy