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

lets have table such (sql server 2005)

pairid childid parentid  0       1      2  1       2      3  2       3      4 

and have cte return dataset:

pairid childid parentid level  0       1      2        2  1       2      3        1  2       3      4        0 

how save initial child id result set instead:

pairid childid parentid level  0       1      2        2  1       1      3        1  2       1      4        0 

so doing keeping original child id , returning instead of other...

this cte query date, works perfectly:

with tester (select a.pairid,                 a.childid,                 a.parentid,                 0 level                                 businesshierarchy                  left outer join businesshierarchy a2                  on a.parentid = a2.childid                 (a2.pairid null)                 union                 select b.pairid, b.childid, b.parentid, oh.level + 1 level                 businesshierarchy b inner join                 tester oh on b.parentid = oh.childid) select pairid, childid, parentid, level   tester x  order level, childid, parentid 

you if first (into variable), use variable later.

declare @startingid int  select     @startingid = a.pairid businesshierarchy  left outer join businesshierarchy a2 on a.parentid = a2.childid (a2.pairid null)   tester (select a.pairid,             a.childid,             a.parentid,             0 level                         businesshierarchy              (a.pairid = @startingid)             union             select b.pairid, b.childid, b.parentid, oh.level + 1 level             businesshierarchy b inner join             tester oh on b.parentid = oh.childid)  select pairid, @startingid, parentid, level  tester x order level, childid, parentid