SQL Server 2019

By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.
Create Table categories(id VARCHAR(50), parentId VARCHAR(50), name VARCHAR(50), path VARCHAR(50), isLastLevel INT);

Insert Into categories Values
('A1',NULL,'Cat 1','Cat 1',0),
('A2','A1','Cat 2','Cat 1 > Cat 2',0),
('A3','A2','Cat 3','Cat 1 > Cat 2 > Cat 3',1),
('A4',NULL,'Cat A','Cat A',0),
('A5','A4','Cat B','Cat A > Cat B',1);
5 rows affected

/* up-hierarchy */
with cte (id, name, parentId, path, ord) as 
(
  select id, name, parentId, path, 1 as ord
  from  categories
  where id = 'A2'
  union all
  select c.id, c.name, c.parentId, c.path, t.ord+1
  from categories c join cte t
  on t.parentId = c.id 
)
select * from cte
order by ord desc;


id
name
parentId
path
ord


A1
Cat 1
null
Cat 1
2

A2
Cat 2
A1
Cat 1 > Cat 2
1


/* down-hierarchy*/
with cte (id, name, parentId, path, ord) as 
(
  select id, name, parentId, path, 1 as ord
  from  categories
  where id = 'A2'
  union all
  select c.id, c.name, c.parentId, c.path, t.ord+1
  from categories c join cte t
  on c.parentId = t.id 
)
select * from cte
order by ord;


id
name
parentId
path
ord


A2
Cat 2
A1
Cat 1 > Cat 2
1

A3
Cat 3
A2
Cat 1 > Cat 2 > Cat 3
2


/* full hierarchy */
with cte(id, name, parentId, path, ord) as 
(
  select id, name, parentId, path, 1 as ord
  from  categories
  where id = 'A2'
  union all
  select c.id, c.name, c.parentId, c.path, t.ord+1
  from categories c join cte t
  on t.parentId = c.id 
),
cte2 (id, name, parentId, path, ord)  as 
(
  select id, name, parentId, path,0 as ord 
  from  categories
  where id = 'A2'
  union all
  select c.id, c.name, c.parentId, c.path, t.ord-1
  from categories c join cte2 t
  on c.parentId = t.id 
)
select id, name, parentId, path from
(
  select *  from cte
  union all
  select *  from cte2
) T
where ord <> 0 /*to aviod id = 'A2' duplication (one from cte and one from cte2)*/
order by ord desc


id
name
parentId
path


A1
Cat 1
null
Cat 1

A2
Cat 2
A1
Cat 1 > Cat 2

A3
Cat 3
A2
Cat 1 > Cat 2 > Cat 3


/* up-hierarchy using self join */
SELECT C1.id, C1.parentId, C1.name, C1.path
FROM categories C1 JOIN categories C2
ON C2.path LIKE CONCAT('%', C1.name, '%')
WHERE C2.id='A2'
ORDER BY len(C1.path)


id
parentId
name
path


A1
null
Cat 1
Cat 1

A2
A1
Cat 2
Cat 1 > Cat 2


/* down-hierarchy using self join */
SELECT C1.id, C1.parentId, C1.name, C1.path
FROM categories C1 JOIN categories C2
ON C1.path LIKE CONCAT('%', C2.name, '%')
WHERE C2.id='A2'
ORDER BY len(C1.path)


id
parentId
name
path


A2
A1
Cat 2
Cat 1 > Cat 2

A3
A2
Cat 3
Cat 1 > Cat 2 > Cat 3


/* full-hierarchy using self join */
SELECT C1.id, C1.parentId, C1.name, C1.path
FROM categories C1 JOIN categories C2
ON C1.path LIKE CONCAT('%', C2.name, '%') OR C2.path LIKE CONCAT('%', C1.name, '%')
WHERE C2.id='A2'
ORDER BY len(C1.path);


id
parentId
name
path


A1
null
Cat 1
Cat 1

A2
A1
Cat 2
Cat 1 > Cat 2

A3
A2
Cat 3
Cat 1 > Cat 2 > Cat 3
id	name	parentId	path
A1	Cat 1	null	Cat 1
A2	Cat 2	A1	Cat 1 > Cat 2
A3	Cat 3	A2	Cat 1 > Cat 2 > Cat 3