Display Parent child hierarchy from one table with parent_id

Transact-SQL
1

I have a table that stores parent_id. So, i can store the hierarchy of a qualification in 1 table. e.g. Qualification will have 2 portions - core and elective. Elective can be further divided into 3 groups - Group A, B and C. These group can further have special areas such as Group A have "commercial cookery" and "working in industry".

2

Please try this code

;with cte
as (
select name as Parent, cast(null as varchar(50)) as child, cast(null as varchar(50)) as grandchild, required, sysuniqid, display_position 
from test 
where Parent_Id is null
),
cte1
as
(
select c.Parent as Parent, cast(t.name as varchar(50)) as child, cast(null as varchar(50)) as grandchild, t.required, t.sysuniqid, t.display_position 
from cte c
inner join test t
on c.sysuniqid    =    t.Parent_Id
),
cte2
as
(
select c.Parent as Parent, cast(c.child as varchar(50)) as child, cast(t.name as varchar(50)) as grandchild, c.required, t.sysuniqid, t.display_position 
from cte1 c
inner join test t
on c.sysuniqid    =    t.Parent_Id
)

select a.Parent, isnull(a.child, b.child) Child, isnull(a.grandchild, b.grandchild) GrandChild, isnull(b.Required, a.required) Required
from cte a
left outer join cte2 b
on a.Parent    =    b.Parent
union 
select a.Parent, a.child, a.grandchild, a.required
from cte a
1 Like
3

Thanks..... That is exactly what i required...... Thanks heaps :smile: