|
AskSQLTeam
Ask SQLTeam Question
0 Posts |
 Posted - 2000-12-28 : 17:40:54
|
David writes "Your answer to:
http://www.4guysfromrolla.com/webtech/sqlguru/q121799-1.shtml
may be simplified and improved by the following SQL.
-------------------------------------------
set nocount on
drop table category
create table category(item_id int identity(1,1),parent_id int,name varchar(50),description varchar(100))
insert category values(0,'Earth','')
insert category values(1,'USA','')
insert category values(2,'California','')
insert category values(3,'Marin County','')
insert category values(4,'San Rafael','')
insert category values(1,'Canada','')
insert category values(1,'England','')
insert category values(4,'Novato','')
insert category values(0,'Mars','')
insert category values(1,'France','')
insert category values(10,'Provence','')
insert category values(9,'BaseAlpha','')
insert category values(5,'Mt. Shasta Ave.','')
-------------------------------------------
create proc ap_get_hierarchy_by_name
@strParentName varchar(50)
as
declare @intIndent int Set @intIndent = 0
create table #Out (indent int, item_id int, parent_id int, orderby varchar(400), name varchar(100))
insert #Out select indent = 0, item_id, parent_id, orderby=name, name from category where name = @strParentName --parent_id = 0
while @@rowcount <> 0
begin
set @intIndent = @intIndent + 1
insert #Out
select indent = @intIndent, P2.item_id, P2.parent_id, orderby = P1.orderby+'-'+P2.name, P2.name
from category P2
JOIN #Out P1
ON P2.parent_id = P1.item_id AND P1.indent = @intIndent - 1
end
set nocount off
select * from #Out order by orderby
drop table #Out
ap_get_hierarchy_by_name 'California'
drop proc ap_get_hierarchy_by_name" |
|
Tree Recursion revisited and improved