I have following data
--------------------------------------------------------------------------------------------------------------
PNode Table
--------------------------------------------------------------------------------------------------------------
Node Index child
0 1 10
0 2 20
10 1 100
10 2 101
100 1 102
100 2 103
102 1 106
103 1 106
101 1 104
101 2 105
104 1 106
105 1 107
so 0 has two child 10 & 20
10 has two child 100 & 101
100 has two child 102 & 103
102 has one child 106
103 has one child 106
101 has two child 104 & 105
104 has one child 106
105 has one child 107
so now i want to use CTE in folowing manner, If i give node = 10 then cte will retrn me all its children and path in following way
Node Path
100 10, 100
101 10, 101
102 10,100,102
103 10,100,103
106 10,100,102,106
106 10,100,103,106
104 10,101,104
106 10,101,104,106
105 10,101,105
107 10,101,105,1076
Please let me know is it possible with recursive CTE.