SQL Server 2005 - CTE, retaining values and inserting in datasets

Question

Lets say you have a table as such (SQL SERVER 2005)

pairID childID parentID
 0       1      2
 1       2      3
 2       3      4

And you have a CTE that return this dataset:

pairID childID parentID level
 0       1      2        2
 1       2      3        1
 2       3      4        0

How do you save the initial child ID so that you would get this result set instead:

pairID childID parentID level
 0       1      2        2
 1       1      3        1
 2       1      4        0

So basically what I am doing is keeping the original child id and returning that instead of the other...

This is the CTE query to date, which works perfectly:

WITH TESTER AS (SELECT a.PairID,
                a.ChildID,
                a.ParentID,
                0 AS Level
                FROM
                BusinessHierarchy AS a 
                LEFT OUTER JOIN BusinessHierarchy AS a2 
                ON a.ParentID = a2.ChildID
                WHERE (a2.PairID IS NULL)
                UNION ALL
                SELECT b.PairID, b.ChildID, b.ParentID, oh.Level + 1 AS Level
                FROM BusinessHierarchy AS b INNER JOIN
                TESTER AS oh ON b.ParentID = oh.ChildID)
SELECT PairID, ChildID, ParentID, Level
 FROM  TESTER AS x
 ORDER BY Level, ChildID, ParentID

Answer 1

You could get that if first (into a variable), then use the variable later.

DECLARE @StartingId int

SELECT
    @StartingId = a.PairID
FROM BusinessHierarchy AS a 
LEFT OUTER JOIN BusinessHierarchy AS a2 ON a.ParentID = a2.ChildID
WHERE (a2.PairID IS NULL)


WITH TESTER AS (SELECT a.PairID,
            a.ChildID,
            a.ParentID,
            0 AS Level
            FROM
            BusinessHierarchy AS a 
            WHERE (a.PairID = @StartingId)
            UNION ALL
            SELECT b.PairID, b.ChildID, b.ParentID, oh.Level + 1 AS Level
            FROM BusinessHierarchy AS b INNER JOIN
            TESTER AS oh ON b.ParentID = oh.ChildID)

SELECT PairID, @StartingId, ParentID, Level
FROM  TESTER AS x
ORDER BY Level, ChildID, ParentID

Answer 2

Untested, but have you tried this?

;WITH My_CTE AS
(
     SELECT
          BH1.PairID,
          BH1.ChildID AS BaseID,
          BH1.ChildID,
          BH1.ParentID,
          0 AS Level
     FROM
          BussinessHierarchy BH1
     LEFT OUTER JOIN BussinessHierarchy BH2 ON
          BH1.ParentID = BH2.ChildID
     WHERE
          BH2.PairID IS NULL
     UNION ALL
     SELECT
          BH3.PairID,
          CTE.BaseID,
          BH3.ChildID,
          BH3.ParentID,
          CTE.Level + 1 AS Level
     FROM
          My_CTE CTE
     INNER JOIN BussinessHierarchy BH3 ON
          BH3.ParentID = CTE.ChildID
     )
SELECT
     PairID,
     BaseID,
     ParentID,
     Level
FROM
     My_CTE
ORDER BY
     Level,
     BaseID,
     ParentID

Answer 3

Your CTE as written just confused me I'm afraid, so I rewrote it as follows and it gives the results you are asking for. Does this meet your requirements?

WITH List (pairID, childId, parentId, level, originalid )
AS
(
    -- Anchor member definition
    select pairID, childID, parentID, 0 as level, ChildID as originalid
    FROM    
    BusinessHeirarchy bh
    where bh.PairID = 0


    UNION ALL

    -- Recursive member definition
    select j.pairId, j.childID, j.parentID, lst.level +1 as level, lst.originalid
    from BusinessHeirarchy j inner join List lst 
    on  (lst.parentId = j.ChildID)

)

select * from list;

You can obviously play with the outputs columns and the order as you need, but that data matches what you want.

One thing to be careful of with my sql is I restricted the anchor query based upon a pairid of zero, since your example data did not have the nulls. This should easy enough to change if you need the null check to pull out your anchor.

Then I simply join on the parent to child relationship with the additional column OriginalId holding the first childId from the anchor query.