sql - flattening product category hierarchy -

i have read on flattening parent child hierarchies finding hard implement it. have product table have childcategorydesc , parentcategorydesc. there 4 levels of hierarchies in cases , want see highest hierarchy level in parentcategorydesc. see below code:

initial code:

select      productid     ,cat.categorydesc 'childcategorydesc'     ,isnull(cat2.categorydesc,cat.categorydesc) 'parentcategorydesc' dbo.product p inner join dbo.category cat on cat.categoryid = p.categoryid left join  dbo.category cat2 on cat.parentcategoryid = cat2.categoryid 

what tried deal hierarchy issue:

--listing 2: static query baased on joins c (   select     a.categoryid    ,a.categoryid a_catid    ,b.categoryid b_catid    ,c.categoryid c_catid    ,d.categoryid d_catid    ,( select count(a.categoryid)     (values(a.categoryid),(b.categoryid),(c.categoryid),(d.categoryid)) d(categoryid) ) lvl   dbo.category   left outer join dbo.category b     on a.categoryid = b.categoryid   left outer join dbo.category c     on b.categoryid = c.categoryid   left outer join dbo.category d     on c.categoryid = d.categoryid    group a.categoryid,b.categoryid,c.categoryid,d.categoryid   ) select categoryid, lvl,   case 1     when lvl     a_catid     when lvl - 1 b_catid     when lvl - 2 c_catid     when lvl - 3 d_catid   end level1,   case 2     when lvl     a_catid     when lvl - 1 b_catid     when lvl - 2 c_catid   end level2,   case 3     when lvl     a_catid     when lvl - 1 b_catid   end level3,   case 4     when lvl     a_catid   end level4 c 

edit:

with initial code data looks this:

all products in category edit end in dresses current code isnt doing that. going 1 level regards parentcategoryid.

check initial code change

new edit

i wrote following code , seems working, can tell me whether have high performance implications or ok go it.

select distinct productdesc     ,cat.categoryid     ,cat.categorydesc     ,cat.categorydesc 'childcategorydesc'     ,case         when cat4.categorydesc not null         cat4.categorydesc          when cat3.categorydesc not null         cat3.categorydesc          when cat2.categorydesc not null         cat2.categorydesc          else cat.categorydesc     end 'parentcategorydesc'     ,cat.[parentcategoryid]     ,cat.[parentcategorytypeid] scv_build.dbo.product p                    inner join scv_build.dbo.category cat on cat.categoryid = p.categoryid    left join dbo.category cat2 on cat.parentcategoryid = cat2.categoryid left join dbo.category cat3 on cat2.parentcategoryid = cat3.categoryid left join dbo.category cat4 on cat3.parentcategoryid = cat4.categoryid