Recursive tree processing.

Original paste:

http://community.csdn.net/Expert/topic/4536/4536413.xml?temp=.2449457

There are two fields in the table:

Child Parent
A NULL
B A
C B
D C
E C

My goal is: How to query all children, including the parent, according to a parent condition.

Like what:

The parent condition is "B" and the result should be:
Child Parent
B A
C B
D C
E C

The parent condition is "C" and the result should be:
Child Parent
C B
D C
E C

Note: There is no limit to the child progression, and any number of children can be arbitrarily arbitrary.

--Test environment
Create table Test (Child varchar (10), Parent varchar (10))
Insert INTO Test select ' A ', NULL
UNION ALL SELECT ' B ', ' A '
UNION ALL SELECT ' C ', ' B '
UNION ALL SELECT ' D ', ' C '
UNION ALL SELECT ' E ', ' C '
--building functions
CREATE Function F_tree (@ Parent varchar (10))
Returns @t Table (sub varchar (10), parent varchar, level int)
Begin
DECLARE @i int
Set @i=1
Insert @t Select *,@i from Test where child =@ Parent
While @ @rowcount <>0
Begin
Set @i=@i+1
Insert @t Select a.*,@i from Test a,@t b
where B. Child =a. Parent and B.level=@i-1
End
Return
End
--Query

Select Child, parent from dbo. F_tree (' B ')
Select Child, parent from dbo. F_tree (' C ')

--The result
Child Parent
---------- ----------
B A
C B
D C
E C

(4 row (s) affected)

Child Parent
---------- ----------
C B
D C
E C

(3 row (s) affected)

--Delete Environment
Drop Table Test
Drop function F_tree