A Recursive Sample

 create table Category (PKey int, Description varchar(20), ParentCategoryKey int)
Insert into Category values(1,'Car',null)

;with mycte as (
Select PKey,Description, PKey as PKey2, ParentCategoryKey ,    Cast(Cast(pkey as varchar(10))+ '-'  as varchar(200))   AS pkeys  from Category 
WHERE ParentCategoryKey is null
Union all
Select c.PKey,c.Description , PKey2 , c.ParentCategoryKey,  Cast(m.pkeys+ Cast(c.pkey as varchar(10))+'-' as varchar(200)) 
from mycte m join  Category c on c.ParentCategoryKey=m.pkey)

Select PKey,Description, ParentCategoryKey, pkeys
 from mycte
'-'+(  select pkeys  from mycte 
where Description='Mustang') like '%-'+Cast(pkey as varchar(10))  +'-%'

drop table Category




Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google+ photo

You are commenting using your Google+ account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )


Connecting to %s