sql server - T-SQL :Parent-Child Hierarchy - based on @Variable -

declare @categories table (         categoryid int,          categoryname varchar(20),         parentid int )           insert @categories(categoryid, categoryname, parentid)             select 1, 'company', null union             select 2, 'dept a', 1 union             select 3, 'dept b', 1 union             select 4, 'da_sub_1', 2 union             select 5, 'da_sub_2', 2 union             select 6, 'da_sub_3', 2 union             select 7, 'da_sub_4', 2 union             select 8, 'da1_i', 4 union             select 9, 'da1_ii', 4         declare @id int = 4         declare @matched int = (select case when parentid = 0 categoryid else parentid end @categories categoryid = @id)          ;with cte (             select categoryid, categoryname, parentid             @categories parentid null             union             select c.categoryid, c.categoryname, c.parentid             @categories c             inner join cte p on p.categoryid = c.parentid      ) 

output

cid cname           pid 1   company         null 2   dept          1 3   dept b          1 4   da_sub_1        2 5   da_sub_2        2 6   da_sub_3        2 7   da_sub_4        2 8   da1_i           4 9   da1_ii          4 

i want make that, can pass variable ,

1) list self + parents 2) list self + siblings 3) list self + children 4) list parents 5) list siblings 6) list children 7) list parents + self + siblings. 8) list self + siblings + children 9) list parents + self + siblings + children (if any). 

how can achieve these things? (this own knowledge trying learn how hierarchy work in t-sql).

thank you

to children :

select * @categories parentid = @id 

to entry itself:

select * @categories categoryid = @id 

to parent:

select parent.*   @categories parent   join @categories self     on self.parentid = parent.categoryid  self.categoryid = @id 

to siblings:

select siblings.*   @categories parent   join @categories self     on self.parentid = parent.categoryid   join @categories siblings     on siblings.parentid = parent.categoryid  self.categoryid = @id 

the easiest way combined results use union all this:

select * @categories parentid = @id  union  select * @categories categoryid = @id  union  select parent.*   @categories parent   join @categories self     on self.parentid = parent.categoryid  self.categoryid = @id  union  select siblings.*   @categories parent   join @categories self     on self.parentid = parent.categoryid   join @categories siblings     on siblings.parentid = parent.categoryid  self.categoryid = @id 

you add hierarchyid column: http://msdn.microsoft.com/en-us/library/bb677290.aspx make queries little less resource intensive, require additional column in table.