Hierarchical data in resultset
-
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.
-
Hello,
this should do the trick (it would be nice to include DDL with your question, so that we don't have to create it - sometimes guessing how it could be... but I hope I got it right). Unguaranteed, but on the given sample it works as expected. If your hierarchy table is large, you should also test performance.
-- supposed DDL + data
CREATE TABLE hiertbl(CategoryID int, CategoryName varchar(50), CategoryFather int)
INSERT INTO hiertbl (CategoryID, CategoryName, CategoryFather) VALUES (1,'video',0)
INSERT INTO hiertbl (CategoryID, CategoryName, CategoryFather) VALUES (2,'DivX',1)
INSERT INTO hiertbl (CategoryID, CategoryName, CategoryFather) VALUES (3,'wmv',1)
INSERT INTO hiertbl (CategoryID, CategoryName, CategoryFather) VALUES (4,'Programming',0)
INSERT INTO hiertbl (CategoryID, CategoryName, CategoryFather) VALUES (5,'web',4)
INSERT INTO hiertbl (CategoryID, CategoryName, CategoryFather) VALUES (6,'asp.net',5)
INSERT INTO hiertbl (CategoryID, CategoryName, CategoryFather) VALUES (7,'ColdFusion',5)
--function that calculates the hierarchy level
CREATE FUNCTION dbo.get_hier_value (@categ int)
RETURNS int
AS
BEGIN
DECLARE @newcat INT, @iter INT
SET @newcat = @categ
SET @iter = 0
WHILE (SELECT CategoryFather FROM hiertbl WHERE CategoryID = @newcat ) > 0
BEGIN
SET @iter = @iter+1
SET @newcat = (SELECT CategoryFather FROM hiertbl WHERE CategoryID = @newcat)
CONTINUE
END
RETURN @iter
END
--this is the actual select which gives required result
SELECT CategoryID, CategoryName, CategoryFather, dbo.get_hier_value(CategoryID) as HierLevel
FROM hiertbl
Disclaimer: I have never really worked with hierarchy tables, but the question was interesting and this is the first solution I came up with. It is very probable that there are better ways to achieve the same result, but since nobody was replying, I thought it would do as something to begin with.
-
Ok Vladan, thank you.
I'll test your code.
-
Vladan, I've just realized that your function only returns the level on the hierarchy, but I need to return all data organized by hierarchy.
I'll try to adapt your code and see what I get.
Thanks.
-
Hello,
I'm sorry, Juliano, but I don't know what you mean by "organized by hierarchy". The only requirement in your original post was "to bring resultset like this", which is what I did... Please, could you explain how do you want to organize the data? Is it something that can be done by simple ORDER BY? I omitted this clause, because I wasn't sure how do you want the data to be ordered.
Try to describe it as precisely as possible, there are several ways how to display such data.
-
VERY cool... I've seen a lot of hierarchical code in my time... this is great... First time I've seen anyone make good use of the CONTINUE statement without them getting stuck in an infinite loop as well. Nice job, Vladan!
If you want to make the output really pretty for the user, you can do this (results window in text mode, please...).
SELECT CategoryID,
LEFT(REPLICATE(SPACE(4),dbo.get_hier_value(CategoryID))+CategoryName,50),
CategoryFather,
dbo.get_hier_value(CategoryID) as HierLevel
FROM hiertbl
--Jeff Moden
RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.Change is inevitable... Change for the better is not.
Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally) -
Well, thanks Jeff
, but in fact CONTINUE is not really necessary in the function - as far as I know, it works the same without it. I had a check there originally, but later realized that WHILE condition can include all you need. CONTINUE would be handy, if I need to skip the last statements and restart the loop immediately in certain situation. Since there is nothing after CONTINUE, it does not do anything. -
A different approach
CREATE TABLE #temp (CategoryID int, CategoryFather int, HierarchicalLevel int)
INSERT INTO #temp SELECT CategoryID, CategoryFather, 0
FROM
DECLARE @rowcount int
SET @rowcount = 1
WHILE @rowcount > 0
BEGIN
UPDATE t
SET t.CategoryFather = SIGN(h.CategoryFather),
t.HierarchicalLevel = t.HierarchicalLevel + 1
FROM #temp t
INNER JOIN h ON h.CategoryID = t.CategoryFather
WHERE t.CategoryFather > 0
SET @rowcount = @@ROWCOUNT
END
SELECT h.CategoryID, h.CategoryName, h.CategoryFather , t.HierarchicalLevel
FROM h
INNER JOIN #temp t ON t.CategoryID = h.CategoryID
DROP TABLE #temp
Far away is close at hand in the images of elsewhere.
Anon. -
oooops !!!
just reread first post, wanted a function
Far away is close at hand in the images of elsewhere.
Anon. -
OK as a function
CREATE FUNCTION dbo.udf_test ()
RETURNS @mytable TABLE (CategoryID int, CategoryName varchar(50), CategoryFather int, HierarchicalLevel int)
BEGIN
DECLARE @temp table (CategoryID int, CategoryFather int, HierarchicalLevel int)
INSERT INTO @temp SELECT CategoryID, CategoryFather, 0 FROM
DECLARE @rowcount int
SET @rowcount = 1
WHILE @rowcount > 0
BEGIN
UPDATE t
SET t.CategoryFather = SIGN(h.CategoryFather),
t.HierarchicalLevel = t.HierarchicalLevel + 1
FROM @temp t
INNER JOIN h ON h.CategoryID = t.CategoryFather
WHERE t.CategoryFather > 0
SET @rowcount = @@ROWCOUNT
END
INSERT INTO @mytable
SELECT h.CategoryID, h.CategoryName, h.CategoryFather , t.HierarchicalLevel
FROM h
INNER JOIN @temp t ON t.CategoryID = h.CategoryID
RETURN
END
Far away is close at hand in the images of elsewhere.
Anon. -
... except to continue
Shows how much I use loops and I didn't read the code well enough.. Thanks for the clarification... --Jeff Moden
RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.Change is inevitable... Change for the better is not.
Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally) -
Here are 2 alternative functions. The first is similar to Vladan's (but slightly simplified), and the second is recursive.
I've not done any speed tests, but I'd be interested in the results if anyone's keen.
--data
if object_id('dbo.hiertbl') is not null drop table hiertbl
CREATE TABLE hiertbl(CategoryID int, CategoryName varchar(50), CategoryFather int)
INSERT INTO hiertbl (CategoryID, CategoryName, CategoryFather) VALUES (1,'video',0)
INSERT INTO hiertbl (CategoryID, CategoryName, CategoryFather) VALUES (2,'DivX',1)
INSERT INTO hiertbl (CategoryID, CategoryName, CategoryFather) VALUES (3,'wmv',1)
INSERT INTO hiertbl (CategoryID, CategoryName, CategoryFather) VALUES (4,'Programming',0)
INSERT INTO hiertbl (CategoryID, CategoryName, CategoryFather) VALUES (5,'web',4)
INSERT INTO hiertbl (CategoryID, CategoryName, CategoryFather) VALUES (6,'asp.net',5)
INSERT INTO hiertbl (CategoryID, CategoryName, CategoryFather) VALUES (7,'ColdFusion',5)
go
--functions
if object_id('dbo.get_hier_value') is not null drop function get_hier_value
go
CREATE FUNCTION dbo.get_hier_value (@categ int) RETURNS int AS
BEGIN
DECLARE @newcat INT, @iter INT
SET @newcat = @categ
SET @iter = -1
WHILE not @newcat = 0
BEGIN
SET @iter = @iter + 1
SELECT @newcat = CategoryFather FROM hiertbl WHERE CategoryID = @newcat
END
RETURN @iter
END
go
if object_id('dbo.get_hier_value2') is not null drop function get_hier_value2
go
CREATE FUNCTION dbo.get_hier_value2 (@categ int) RETURNS int AS
BEGIN
DECLARE @newcat INT, @iter INT
SELECT @newcat = CategoryFather FROM hiertbl WHERE CategoryID = @categ
if @newcat = 0
set @iter = 0
else
set @iter = 1 + dbo.get_hier_value2 (@newcat)
RETURN @iter
END
go
--calculation
select *, dbo.get_hier_value(CategoryID) as level from hiertbl
select *, dbo.get_hier_value2(CategoryID) as level from hiertbl
--tidy up
if object_id('dbo.hiertbl') is not null drop table hiertbl
if object_id('dbo.get_hier_value') is not null drop function get_hier_value
if object_id('dbo.get_hier_value2') is not null drop function get_hier_value2
Ryan Randall
Solutions are easy. Understanding the problem, now, that's the hard part.
-
Another country hear from! Also, very neat, David. Interesting use of SIGN as a "lump detector".
Juliano... has one of these 3 solutions provided you with the answer you needed? They have for me and I wasn't even looking for a solution for this... these are definitely keepers.
--Jeff Moden
RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.Change is inevitable... Change for the better is not.
Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)
Viewing 13 posts - 1 through 13 (of 13 total)
You must be logged in to reply to this topic. Login to reply