Hierarchical data in result set

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.

I didnot quite understand your question!

Is the "HierarchicalLevel" value calculated ? or it was stored in the DB?

IF it is the first case:

you'd better get the date first in the application layer,and then use recursion ways to calculated the "HierarchicalLevel" value .

it is easy to calculate the value in the application layer

|||

Shieldy, the HierarchicalLevel is a number to indicate in which node level the registry is.

So, imagine a family tree.

Grandfather __ Uncle
|
Father Daughter
|
Son

Grandfather is the level 0, the most high level on this hierarchy. Father and Uncle are the level 1, because they are under the level 0, its parent. And Son and Daughter are the level 2.

Just a detail, if I search for relationships about father, this changes.

Father -- Daughter
|
Son

In this case Father is the level 0, Daughter and Son are the level 1.

If you don't understand this examples, I can try explain it better.

|||

Hi Juliano,

The sequence of code that does what you need is inserted bellow. I wrote it based on the "The Guru's Guide To Transact-SQL" book.

DECLARE @.Categories TABLE (CategoryID Int, CategoryName Varchar(20), CategoryFather Int)

INSERT INTO @.Categories
VALUES (1, 'Video', 0)
INSERT INTO @.Categories
VALUES (2, 'DivX', 1)
INSERT INTO @.Categories
VALUES (3, 'WMV', 1)
INSERT INTO @.Categories
VALUES (4, 'Programming', 0)
INSERT INTO @.Categories
VALUES (5, 'Web', 4)
INSERT INTO @.Categories
VALUES (6, 'ASP.Net', 5)
INSERT INTO @.Categories
VALUES (7, 'ColdFusion', 5)

DECLARE @.TempCategories TABLE (HierarchicalLevel Int, CategoryID Int, CategoryFather Int)
INSERT INTO @.TempCategories (HierarchicalLevel, CategoryID, CategoryFather)
SELECT 1, CategoryID, CategoryFather
FROM @.Categories

WHILE (@.@.RowCount > 0)
BEGIN
INSERT INTO @.TempCategories (HierarchicalLevel, CategoryID, CategoryFather)
SELECT DISTINCT c1.HierarchicalLevel+1, c2.CategoryID, c1.CategoryFather
FROM @.TempCategories c1
INNER JOIN @.TempCategories c2 ON c1.CategoryID = c2.CategoryFather
WHERE c1.HierarchicalLevel=(SELECT MAX(HierarchicalLevel) FROM @.TempCategories)
AND c1.CategoryFather<>c2.CategoryID
END

SELECT t.CategoryID, c.CategoryName, c.CategoryFather, HierarchicalLevel = MAX(t.HierarchicalLevel)-1
FROM @.TempCategories t
INNER JOIN @.Categories c ON t.CategoryID=c.CategoryID
GROUP BY t.CategoryID, c.CategoryName, c.CategoryFather

Hope it helps!

|||

hi there

it looks like you need

to implement this using a self join.

Using Self-Joins

A table can be joined to itself in a self-join. For example, you can use a self-join to find out the authors in Oakland, California who live in the same ZIP Code area.

Because this query involves a join of the authors table with itself, the authors table appears in two roles. To distinguish these roles, you must give the authors table two different aliases (au1 and au2) in the FROM clause. These aliases are used to qualify the column names in the rest of the query. This is an example of the self-join Transact-SQL statement:

USE pubs SELECT au1.au_fname, au1.au_lname, au2.au_fname, au2.au_lname FROM authors au1 INNER JOIN authors au2 ON au1.zip = au2.zip WHERE au1.city = 'Oakland' ORDER BY au1.au_fname ASC, au1.au_lname ASC 

Here is the result set:

au_fname au_lname au_fname au_lname -- - -- Dean Straight Dean Straight Dean Straight Dirk Stringer Dean Straight Livia Karsen Dirk Stringer Dean Straight Dirk Stringer Dirk Stringer Dirk Stringer Livia Karsen Livia Karsen Dean Straight Livia Karsen Dirk Stringer Livia Karsen Livia Karsen Marjorie Green Marjorie Green Stearns MacFeather Stearns MacFeather (11 row(s) affected) 

To eliminate the rows in the results in which the authors match themselves and to eliminate rows that are identical, except the order of the authors is reversed, make this change to the Transact-SQL self-join query:

USE pubs SELECT au1.au_fname, au1.au_lname, au2.au_fname, au2.au_lname FROM authors au1 INNER JOIN authors au2 ON au1.zip = au2.zip WHERE au1.city = 'Oakland' AND au1.state = 'CA' AND au1.au_id < au2.au_id ORDER BY au1.au_lname ASC, au1.au_fname ASC 

Here is the result set:

au_fname au_lname au_fname au_lname -- -- -- Dean Straight Dirk Stringer Dean Straight Livia Karsen Dirk Stringer Livia Karsen (3 row(s) affected) 

It is now clear that Dean Straight, Dirk Stringer, and Livia Karsen all have the same ZIP Code and live in Oakland, California.