hierarchical data - SQL Server Tree Query -

i need ms sql server query. i’m not of dba. have application organization table made of parent-child relationship:

create table [dbo].[organizations](     [orgpk] [int] identity(1,1) not null,     [orgparentfk] [int] null,     [orgname] [varchar](200) not null, constraint [pk__organizations] primary key clustered 

sample data looks this:

orgpk,   orgparentfk, orgname 1,  0,  corporate 2,  1,  department 3,  1,  department b 4,  2,  division 1 5,  2,  division 2 6,  3,  division 1 7,  6,  section 1 8,  6,  section 2 

i'm trying generate query returns org path based on given orgpk. example if given orgpk = 7 query return 'corporation/department b/division 1/section 1'

if give orgpk = 5 return string 'corporation/department a/division 2'

thank assistance.

with  organizationsh (orgparentfk, orgpk, orgname, level, label) (     select orgparentfk, orgpk, orgname, 0, cast(orgname varchar(max)) label     organizations     orgparentfk null     union     select o.orgparentfk, o.orgpk, o.orgname, level + 1,  cast(h.label + '/' + o.orgname  varchar(max)) label     organizations o join organizationsh h on o.orgparentfk = h.orgpk )  select orgparentfk, orgpk, orgname, level, label organizationsh orgpk = 5 

h/t marc_s