Hierarchy Table - SQL Server Forums

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

SQL Server 2005 Forums

Transact-SQL (2005)

Hierarchy Table

Author

Topic

Jiggerman
Starting Member

4 Posts

Posted - 2007-06-16 : 13:07:20

I have a table which I want displayed in a hierarchy form. I have read several post but no solutions yet. Below is my table format:

ParentID with -1 represents root.
A root can have multiple children, grand children, great grand children.

IssueID GroupID    Name    ParentID Depth Order

     1     1         Education    -1      1         1

     2     2         Law           -1       1        2

    3       3        Public        -1       1        3

    4      1         School       1        2        1

    5       1        Child        4        3      1

    6       1       Programs     4       3        2

   7       1        Recreation      6       4        1

   8      8         Health          -1        1    4

   9      8         illness           8      2     1

   10      1       Cycling          6      4       2

   11      1       Day Care       5      4      1

Query should return (does not have to be indented, but table should return rows in the order below):

Education
    School
        Child
            Day Care
        Programs
            Recreation
            Cycling
Law
Public
Health
    illness

(Note Recreation & Cycling are display by Order under their parent)

Help with code or suggestions are greatly appreciated

Vinnie881
Master Smack Fu Yak Hacker

1231 Posts

Posted - 2007-06-16 : 14:38:55

There are many ways to do this with a fixed number of child categories, Just search the web for Hierarchy catagory sql. But this is byfar the coolest way i have seen to do it in 1 query w/ unlimited child nodes

It's written for mysql, but will work fine in sql2000/2005 with minor modification.

http://dev.mysql.com/tech-resources/articles/hierarchical-data.html

Jiggerman
Starting Member

4 Posts

Posted - 2007-06-17 : 13:41:24

I have read it, but I don't get it.
Is there any simple way of getting this done?
Please feedback is greatly welcomed.

blindman
Master Smack Fu Yak Hacker

2365 Posts

Posted - 2007-06-17 : 20:20:12

That link has a good example of the nested set model, but it is a horrible example of how to query the traditional adjacency model.

Try this method instead:
http://sqlblindman.googlepages.com/returningchildrecords

e4 d5 xd5 Nf6

Jiggerman
Starting Member

4 Posts

Posted - 2007-06-17 : 21:38:29

Thanks for your reply, I modified the query and it works a lil'bit for my table. Below is the difference here:

What I expect:
Education
School
Child
Day Care
Programs
Recreation
Cycling
Law
Public
Health
illness

What it gives me:(note child and program chrildren are out of order)
Education
School
Child
Programs
Day Care
Recreation
Cycling
Law
Public
Health
illness

This is my script below:

--This variable will hold the parent record ID who's children we want to find.
declare @RecordID int
set @RecordID = -1

--This table will accumulate our output set.
declare @RecordList table (IssueID int, GroupID int, ParentID int, IssueName nvarchar(150), Depth int, Order int)

--Seed the table with the @RecordID value, assuming it exists in the database.
insert into @RecordList (IssueID, GroupID, ParentID, IssueName, Depth, Order)
select IssueID, GroupID, ParentID, IssueName, Depth, Order
from Issue
where Issue.ParentID = @RecordID

--Add new child records until exhausted.
while @@RowCount > 0
insert into @RecordList (IssueID, GroupID, ParentID, IssueName, Depth, Order)
select Issue.IssueID, Issue.GroupID, Issue.ParentID, Issue.IssueName, Issue.Depth, Issue.Order
from Issue
inner join @RecordList RecordList on Issue.ParentID = RecordList.IssueID
where not exists (select * from @RecordList CurrentRecords where CurrentRecords.IssueID = Issue.IssueID)

--Return the result set
select IssueID, GroupID, ParentID, IssueName, Depth, Order
from @RecordList
order by GroupID

My question now is do I have to write another Inner join to get the "great grand" children to get displayed in the right place?
Child
Day Care
Programs
Recreation
Cycling

blindman
Master Smack Fu Yak Hacker

2365 Posts

Posted - 2007-06-18 : 09:21:08

One way would be to define a varchar variable called INDENT. Set it to a zero-length string to begin with and then extend it five spaces in each iteration of the WHILE loop. Then prepend it to the issue name in your temporary table.
Or, create an integer value called INDENTLEVEL starting with 0, incremented in each loop, and stored in the DEPTH column of your temporary table. Then, when you print your result set you can use the DEPTH value to determine how many tabs to prepend.

e4 d5 xd5 Nf6

Jiggerman
Starting Member

4 Posts

Posted - 2007-06-19 : 12:37:11

Thanks for you suggestions.
I used a control called Trreview in ASP.NET 2.0 to get this done. It required a lil bit of programming to conect to the database.
This process did not require a function or stored procedure.
Thanks for your help.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources