Flattening Hierarchy series using SQL Server CTE

database, hierarchy, sql, sql-server, t-sql

Solution

with cte as (
    select ID, Name, level, Parent_id
    from family

    union all

    select cte.ID, cte.Name, cte.level, family.Parent_id
    from cte
    inner join family on cte.Parent_id = family.ID
)
select cte.ID as Child_ID, f2.Parent_ID, cte.level as Child_level, cte.Name, isnull(cte.Parent_id, cte.ID) as parent_ID, isnull(f.level, cte.level) as level
from cte
left outer join family f on cte.Parent_id = f.ID
inner join family f2 on cte.id = f2.ID
order by 1, 5

Problem

Looking for SQL Server CTE example to create hierarchy in such a way that I can output all the series like flattening the each hierarchy. e.g in family tree if root start from grand Parent I need list of each family memeber's hierarchy list with member details + parent's row details like ``` child1 row child1 +Parent row child1 + Grand parent Row child2 Row child2 + parent row child2 + grand parent's row and so on ``` ``` CREATE TABLE Family(id int NULL, Name varchar(20) null, Parent_id int NULL, level int NULL ) INSERT INTO Family VALUES (1, 'Grand Parent',NULL, 1), (2, 'Parent' , 1, 2), (3, 'Child1' , 2, 3), (4, 'Child2' , 2, 3) select * from Family; id Name Parent_id level 1 Grand Parent NULL 1 2 Parent 1 2 3 Child1 2 3 4 Child2 2 3 ``` This is what I can do at this point. Details of the parent row are in column 5 and 6. ``` with cte as ( select ID,Parent_id,level,Name,id as parent_id,level, 'a' as type from family --where ID=3 union all select f.ID,f.Parent_id,f.level,f.Name,c.id as parent_id,c.level, 'r' as type from family f inner join cte c on f.parent_id=c.id ) select * from cte order by id ``` This is what the results should be. (note column 5 and 6) ``` Child_ID Parent_id Child_level Name parent_id level 1 NULL 1 Grand Parent 1 1 2 1 2 Parent 2 2 2 1 2 Parent 1 1 3 2 3 Child1 1 1 3 2 3 Child1 2 2 3 2 3 Child1 3 3 4 2 3 Child2 4 3 4 2 3 Child2 2 2 4 2 3 Child2 1 1 ``` Thanks in advance.

Original source