A Recursive Sample


  
 
 create table Category (PKey int, Description varchar(20), ParentCategoryKey int)
Insert into Category values(1,'Car',null)
,(2,'Truck',null)
,(3,'Ford',1)
,(4,'Mustang',3)
,(5,'Lexus',1)
,(6,'pickup',2)

;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
WHERE   
'-'+(  select pkeys  from mycte 
where Description='Mustang') like '%-'+Cast(pkey as varchar(10))  +'-%'
 

drop table Category

 

https://social.msdn.microsoft.com/Forums/sqlserver/en-US/e263a3cb-af26-4a4a-991a-ffe855a7d4ae/query-tables-parent-key-and-ancestors?forum=transactsql

Advertisements


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 )

Twitter picture

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

Facebook photo

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

Google+ photo

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

Connecting to %s