SQL Server: get all parent child urls from one table

Question

I have a table that looks like this:

Slug | Parent | MetaTitle | MetaDescription | MetaKeywords

A site URL is created by adding a pool to the parent if the parent is not null. Therefore, if I have such data:

Slug            | Parent
-----------------------------------------
Home              null
About             null
Our-Company       About
Our-History       Our-Company

I am looking to be able to run a query that will deliver me

/Home
/About
/About/Our-Company
/About/Our-Company/Our-History

Is it possible? Any help would be great !!!

Answer 1

It is possible to use a recursive CTE :

declare @URLTable table (
 Slug varchar(50),
 Parent varchar(50)
)

insert into @URLTable
 (Slug, Parent)
 values
 ('Home', null),
 ('About', null),
 ('Our-Company', 'About'),
 ('Our-History', 'Our-Company')


;with cteURLs as (
 select Slug, cast('/' + Slug as varchar(1000)) as URL
     from @URLTable
     where Parent is null
 union all
 select u.Slug, cast(c.URL + '/' + u.Slug as varchar(1000)) as URL
     from @URLTable u
         inner join cteURLs c
             on u.Parent = c.Slug
)
select URL from cteURLs

Conclusion:

URL
-----------------
/Home
/About
/About/Our-Company
/About/Our-Company/Our-History