Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

 All Forums
 General SQL Server Forums
 New to SQL Server Programming
 Multilevel Parent-Child Relation in single table

Author  Topic 

uba
Starting Member

5 Posts

Posted - 2013-11-10 : 16:09:15
I have a table called "College". In a table, I have to create a structure for multilevel parent-child relationship

For Example,

1) State has number of colleges, Number of colleges has Number of dept. , Number of dept. has no. of subjects, no. of subject has number of chapters and the hierarchy goes on.

Expected Output is,
College 1
Dept 1
subject 1
subject 2
subject 3
Dept 2
Dept 3
Subject 1
subject 2
College 2
Dept 1
subject 1
Dept 2
subject 1
subject 2
subject 3
Dept 3
Subject 1
subject 2

I tried in so many ways, I do not know how to query in single table

waterduck
Aged Yak Warrior

982 Posts

Posted - 2013-11-10 : 21:24:02
hi, can you provide the table structure together with the sample data? its hard to imagine how your scenario was.
Go to Top of Page

uba
Starting Member

5 Posts

Posted - 2013-11-11 : 01:01:36
quote:
Originally posted by waterduck

hi, can you provide the table structure together with the sample data? its hard to imagine how your scenario was.



Sorry, I didn't make myself clear.

In a SQL table, I will have parent, child, sub-child, sub-sub-child, etc. etc. In short, parent can have ‘n’ level of child records.

For Example,



I need to write a SP to get the expected output.

Note: To achieve this logic, we can alter the table structure if needed.

Sample Data: 

create table College(ID int, Name varchar(50), ParentID int)

insert into College values(1,'College 1',0)
insert into College values(2,'College 2',0)
insert into College values(3,'Department 1-1',1)
insert into College values(4,'Department 1-2',1)
insert into College values(5,'Department 2-1',2)
insert into College values(6,'Subject 1-1-1',3)
insert into College values(7,'Subject 1-2-1',4)
insert into College values(8,'Subject 1-2-2',4)
insert into College values(9,'Chapter 1-2-1-1',7)
insert into College values(10,'Chapter 1-2-1-2',7)
Go to Top of Page

waterduck
Aged Yak Warrior

982 Posts

Posted - 2013-11-11 : 01:14:51
[code]
declare @College table(ID int, Name varchar(50), ParentID int)

insert into @College values(1,'College 1',0)
insert into @College values(2,'College 2',0)
insert into @College values(3,'Department 1-1',1)
insert into @College values(4,'Department 1-2',1)
insert into @College values(5,'Department 2-1',2)
insert into @College values(6,'Subject 1-1-1',3)
insert into @College values(7,'Subject 1-2-1',4)
insert into @College values(8,'Subject 1-2-2',4)
insert into @College values(9,'Chapter 1-2-1-1',7)
insert into @College values(10,'Chapter 1-2-1-2',7)

;with cte as(
select
ID,
Name,
ParentID,
lvl = 0,
Sortid = cast(ID as varchar(max))
from @College
where parentid = 0
union all
select
a.ID,
a.Name,
a.ParentID,
lvl = lvl + 1,
Sortid = Sortid + cast(a.ID as varchar(max))
from @College a
join cte b
on a.ParentID = b.ID
)
select
id,
replicate(' ', lvl) + name,
ParentID
from cte
order by Sortid

[/code]
edit formatting
Go to Top of Page

uba
Starting Member

5 Posts

Posted - 2013-11-11 : 02:48:53
Tons of Thanks Waterduck and It is perfectly working!

~Kudos to you
Go to Top of Page

uba
Starting Member

5 Posts

Posted - 2013-11-11 : 09:59:38
Hi,

I am trying to convert the CTE as SP. But, I am wondering how to declare a SortId vaiable. I am getting errors in my sp,
Msg 207, Level 16, State 1, Procedure collagesp, Line 9
Invalid column name 'Sortid'.
Msg 8158, Level 16, State 1, Procedure collagesp, Line 5
'CTEExample' has more columns than were specified in the column list


My SP:
create procedure collagesp
as
declare @Sortid as int
begin
with CTEExample(ID, Name, ParentID) AS
(
select ID, Name, ParentID, Sortid = cast(ID as varchar(max)) from College where parentid = 0
union all
select a.ID, a.Name, a.ParentID, Sortid = Sortid + cast(a.ID as varchar(max))from College a join College b on a.ParentID = b.ID
)
select id, name, ParentID from CTEExample order by Sortid
end
go
Go to Top of Page

uba
Starting Member

5 Posts

Posted - 2013-11-11 : 22:43:03
I got it!

I missed to define column definition and the wrong cte name in the join.
Go to Top of Page
   

- Advertisement -