Here is a simple example of a family tree:
Copy Code code as follows:
DECLARE @TT TABLE (ID int,relation varchar (), Name varchar (), ParentID int)
INSERT @TT SELECT 1, ' great grandfather ', ' Thomas Bishop ', null UNION all
SELECT 2, ' Grand Mom ', ' Elian Thomas Wilson ', and 1 UNION all
SELECT 3, ' Dad ', ' James Wilson ', 2 UNION all
SELECT 4, ' Uncle ', ' Michael Wilson ', 2 UNION all
SELECT 5, ' Aunt ', ' Nancy Manor ', 2 UNION all
SELECT 6, ' Grand Uncle ', ' Michael Bishop ', 1 UNION all
SELECT 7, ' Brother ', ' David James Wilson ', 3 UNION all
SELECT 8, ' Sister ', ' Michelle Clark ', 3 UNION all
SELECT 9, ' Brother ', ' Robert James Wilson ', 3 UNION all
SELECT, ' Me ', ' Steve James Wilson ', 3
----------Query---------------------------------------
; With Familytree
As (
SELECT *, CAST (NULL as VARCHAR ()) as ParentName, 0 as Generation from @TT
WHERE ParentID is NULL
UNION All
SELECT Fam.*,familytree.name as ParentName, Generation + 1 from @TT as Fam
INNER JOIN familytree on fam.parentid = familytree.id
) SELECT * from Familytree
Output:
Hope to help you
Author:petter Liu
