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 relationshipFor 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 2College 2 Dept 1 subject 1 Dept 2 subject 1 subject 2 subject 3 Dept 3 Subject 1 subject 2I 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. |
|
|
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) |
|
|
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, ParentIDfrom cteorder by Sortid[/code]edit formatting |
|
|
uba
Starting Member
5 Posts |
Posted - 2013-11-11 : 02:48:53
|
Tons of Thanks Waterduck and It is perfectly working!~Kudos to you |
|
|
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 9Invalid column name 'Sortid'.Msg 8158, Level 16, State 1, Procedure collagesp, Line 5'CTEExample' has more columns than were specified in the column listMy SP:create procedure collagespasdeclare @Sortid as intbeginwith 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 Sortidendgo |
|
|
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. |
|
|
|
|
|