SQL Server 2017

By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.

CREATE TABLE assets
             (parentassetid integer,
              childassetid integer,
              assetname nvarchar(8));
INSERT INTO assets (parentassetid, childassetid, assetname)
            VALUES
(84,            2,            'abc'),
(35,            1,            'cdf'),
(956,           35,           'PARENT35'),
(84,            1,            'ghi'),
(956,           3,            'PARENT3'),
(35,            3,            'jkl'),
(956,           84,           'PARENT84'),
(3,             5,            'mno');
8 rows affected

with cte as (
      select a.parentassetid, a.childassetid, a.assetname,
             convert(varchar(max), concat(format(a.parentassetid, '0000'), format(a.childassetid, '0000'))) as path, 1 as lev
      from assets a
      where not exists (select 1 from assets ap where a.parentassetid = ap.childassetid)
      union all
      select a.parentassetid, a.childassetid, a.assetname,
             convert(varchar(max), concat(cte.path, '/', format(a.childassetid, '0000'))), lev + 1
      from cte join
           assets a
           on cte.childassetid = a.parentassetid
      where lev < 10
     )
select *
from cte
order by path


parentassetid
childassetid
assetname
path
lev


956
3
PARENT3
09560003
1

3
5
mno
09560003/0005
2

956
35
PARENT35
09560035
1

35
1
cdf
09560035/0001
2

35
3
jkl
09560035/0003
2

3
5
mno
09560035/0003/0005
3

956
84
PARENT84
09560084
1

84
1
ghi
09560084/0001
2

84
2
abc
09560084/0002
2


with cte as (
      select a.parentassetid, a.childassetid, a.assetname,
             convert(varchar(max), concat(format(a.parentassetid, '0000'), format(a.childassetid, '0000'))) as path, 1 as lev
      from assets a
      where not exists (select 1 from assets ap where a.parentassetid = ap.childassetid)
      union all
      select a.parentassetid, a.childassetid, a.assetname,
             convert(varchar(max), concat(cte.path, '/', format(a.childassetid, '0000'))), lev + 1
      from cte join
           assets a
           on cte.childassetid = a.parentassetid
      where lev < 10
     )
select cte.*
from (select cte.*,
             row_number() over (partition by parentassetid, childassetid order by lev asc) as seqnum
      from cte
     ) cte
where seqnum = 1
order by path


parentassetid
childassetid
assetname
path
lev
seqnum


956
3
PARENT3
09560003
1
1

3
5
mno
09560003/0005
2
1

956
35
PARENT35
09560035
1
1

35
1
cdf
09560035/0001
2
1

35
3
jkl
09560035/0003
2
1

956
84
PARENT84
09560084
1
1

84
1
ghi
09560084/0001
2
1

84
2
abc
09560084/0002
2
1

parentassetid	childassetid	assetname	path	lev
956	3	PARENT3	09560003	1
3	5	mno	09560003/0005	2
956	35	PARENT35	09560035	1
35	1	cdf	09560035/0001	2
35	3	jkl	09560035/0003	2
3	5	mno	09560035/0003/0005	3
956	84	PARENT84	09560084	1
84	1	ghi	09560084/0001	2
84	2	abc	09560084/0002	2