use pubs
set nocount on
go

create table junk
	(CategoryID int
	,ParentCategoryID int
	,Category varchar(30))
go

insert junk
select 65, NULL, 'Economic Development' union all
select 66, 65, 'A Wealth of Opportunity' union all
select 67, NULL, 'Top Category' union all
select 69, 67, 'test 3' union all
select 73, NULL, 'A new category' union all
select 74, 73, 'test' union all
select 75, 73, 'a test' union all
select 80, 75, 'a 3rd level test' union all
select 81, 80, 'a 4th level test'

go

declare @tb table 
	(CategoryID int
	,hierarchy varchar(7900)
	,category varchar(30)
	,lev int)

declare @level int
set @level = 0

insert @tb (CategoryID, hierarchy, category, lev)
select	categoryid
	,category
	,category
	,@level
from	junk
where	parentCategoryid is null

--loop through an entire nesting level at once
while @@rowcount > 0
begin
	set @level = @level + 1

	insert @tb (CategoryID, hierarchy, category, lev)
	select	a.categoryid
		,b.hierarchy + '.' + a.category
		,a.category
		,@level
	from	junk a
	join	@tb b
			on b.CategoryID = a.parentCategoryid

	--prevent circular references
	left join @tb ex
			on ex.CategoryID = a.Categoryid
	where	ex.CategoryID is null

	and	b.lev+1 = @level

end

--return results in hierarchal, alpabetical order
select	category
	,space(lev*3) + category [visual hierarchy]
from @tb 
order by hierarchy

go
drop table junk