Sql Server 2008 R2: Recursive Query

This is the follow up question of : Prepare a recursive query I have the table with the two columns namely cola and colb as shown below: Table : Test create table Test ( cola int,

Solution 1:

;WITH CTE AS
(
   SELECT COLA,COLB
                 ,','+CAST(COLA AS VARCHAR(MAX))+',' AS CHCK 
                , 1 as lvl FROM #Test WHERE COLA=1
   UNION ALL
   SELECT C1.COLA,C1.COLB  ,C.CHCK+CAST(C1.cola AS VARCHAR(MAX))+',' 
                            , c.lvl+1 
   FROM CTE C INNER JOIN #Test C1 ON  C.colb = C1.cola
    WHERE CHARINDEX(','+CAST(C.colb AS VARCHAR(MAX))+',',C.CHCK)=0 

),
cte2 as (
select * , ROW_NUMBER() over (partition by colb order by lvl)as rn From CTE
)
select cola,colb,lvl from cte2 where rn = 1