SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 General SQL Server Forums
 New to SQL Server Programming
 Multilevel Parent-Child Relation in single table
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

uba
Starting Member

USA
5 Posts

Posted - 11/10/2013 :  16:09:15  Show Profile  Reply with Quote
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

Malaysia
961 Posts

Posted - 11/10/2013 :  21:24:02  Show Profile  Reply with Quote
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

USA
5 Posts

Posted - 11/11/2013 :  01:01:36  Show Profile  Reply with Quote
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

Malaysia
961 Posts

Posted - 11/11/2013 :  01:14:51  Show Profile  Reply with Quote

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


edit formatting

Edited by - waterduck on 11/11/2013 01:17:55
Go to Top of Page

uba
Starting Member

USA
5 Posts

Posted - 11/11/2013 :  02:48:53  Show Profile  Reply with Quote
Tons of Thanks Waterduck and It is perfectly working!

~Kudos to you

Edited by - uba on 11/11/2013 02:49:31
Go to Top of Page

uba
Starting Member

USA
5 Posts

Posted - 11/11/2013 :  09:59:38  Show Profile  Reply with Quote
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

USA
5 Posts

Posted - 11/11/2013 :  22:43:03  Show Profile  Reply with Quote
I got it!

I missed to define column definition and the wrong cte name in the join.
Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.08 seconds. Powered By: Snitz Forums 2000