Hierarchical data in result set

[julianonunes]

How can I create a function that returns hierarchical data from a table with this structure:

- CategoryID
- CategoryName
- CategoryFather

I want to bring the result set like this...

CategoryID | CategoryName | CategoryFather | HierarchicalLevel
1 | Video | 0 | 0
2 | DivX | 1 | 1
3 | WMV | 1 | 1
4 | Programming | 0 | 0
5 | Web | 4 | 1
6 | ASP.Net | 5 | 2
7 | ColdFusion | 5 | 2


How can I do this? Does anybody has a sample code? I need this on SQL Server 2000 and if it's possible (but not too necessary) in SQL Server 2005.

Thanks.

 

[mrdenny]

You would need to write a basic user function with a loop to handle this. Functions by them selves are not effecient, functions with loops or cursors are VERY inefficient.

create function ...
   (@CategoryID INT)
as
RETURNS INT
BEGIN
    DECLARE @ParentID int
    DECLARE @ChildID int
    DECLARE @Level int
    SET @ParentID = -1
    SET @ChildID = @CategoryID
    SET @Level = 0
    WHILE @ParentID <> 0
    BEGIN
        select @ParentID = CategoryFather,
            @Level = @Level + 1,
            @ChildID = CategoryFather
        from Table1
        where CategoryID = @ChildID
    END
    RETURN @Level
END

This code isn't tested but should get you going in the correct direction.

Just an FYI, People usually use Parent not father as a record's parent isn't gender specific. Typically the only time you'll see Father or Mother for a record would be a family tree senerio where the record has two parents (or some other senerio where a record has two parents). Most typically the column would be called Parent_CagegoryID or something like that.

Denny
MCSA (2003) / MCDBA (SQL 2000) / MCTS (SQL 2005)

--Anything is possible. All it takes is a little research. (Me)

http://www.mrdenny.com

 

[julianonunes]

Denny, I don't want to return the CategoryID only, I have to return the data like this:

CategoryID | CategoryName | CategoryFather | HierarchicalLevel
1 | Video | 0 | 0
2 | DivX | 1 | 1
3 | WMV | 1 | 1
4 | Programming | 0 | 0
5 | Web | 4 | 1
6 | ASP.Net | 5 | 2
7 | ColdFusion | 5 | 2

I need all this info, ordered by Parent and if it has childs, show them. If a child is also a parent, show its childs too, and so on.

It's a kind of data tree.

Thanks.

 

[r937]

do a search for "joe celko's nested set model"

what you want to display is neither easy nor efficient to do with the adjacency model (that's what it's called when you use parent_id) -- unless you can decide that there is a certain maximum depth to the tree that you want to display, i.e. no more than N levels down from the top, where N is some small number less than, say two dozen

r937.com | rudy.ca

 

[julianonunes]

The maximum depth is 3 childs.

Main
|_ child
|_ child
|_ child

 

[r937]

ah, that's excellent

select ...
  from Table1 as level0
left outer
  join Table1 as level1
    on level1.CategoryFather
     = level0.CategoryID 
left outer
  join Table1 as level2
    on level2.CategoryFather
     = level1.CategoryID 
left outer
  join Table1 as level3
    on level3.CategoryFather
     = level2.CategoryID      
 where level0.CategoryFather  = 0

r937.com | rudy.ca