[SOLVED] SQL recursion + column concatenation

SQL recursion + column concatenation

I've got a self referencing table (in SQL Server):

Page
==========
Id: int
RelativeUrl: nvarchar(max)
ParentId: int -> FK to pageId

Example data:

ID | RelativeUrl | ParentId
===============================
1  | /root       | null
2  | /test1      | 1
3  | /test2      | 1
4  | /test3      | 1
5  | /test1-1    | 2
6  | /test1-2    | 2
7  | /test1-1-1  | 5

I want to create an sql query to retrieve all pages of the table with FULL relative url. I thought about using a recursive SQL query, but don't know how to concat the relativeurl to make it a full relative url.

Wanted result:

ID | FullRelativeUrl                | ParentId
===================================================
1  | /root                          | null
2  | /root/test1                    | 1
3  | /root/test2                    | 1
4  | /root/test3                    | 1
5  | /root/test1/test1-1            | 2
6  | /root/test1/test1-2            | 2
7  | /root/test1/test1-1/test1-1-1  | 5

Solution

You can use a recursive CTE:

with cte as (
      select id, convert(varchar(max), relativeurl) as url, 1 as lev
      from page
      where parentid is null
      union all
      select p.id, concat(cte.url, p.relativeurl), lev + 1
      from cte join
           page p
           on p.parentid = cte.id
     )
select cte.*
from cte;

Here is a db<>fiddle.