SQL Server: get all parent child URL's from a single table

Question

I have a table that looks like this:

Slug | Parent | MetaTitle | MetaDescription | MetaKeywords

The Url's on the site are built by appending the slug to the parent as long as the parent is not null. So if I have data like so:

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

I am looking to be able to run a query that would get me

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

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

Answer 1

It's possible using 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

The output is:

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