SQL Server 2017

By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.
CREATE TABLE dbo.t (W_ID int NOT NULL,
							Title varchar(3) NOT NULL,
							Parent_id int NULL,
							View_Parent_id varchar(100) NULL);
INSERT INTO dbo.t
VALUES (1,'AAA',NULL,NULL),
	   (2,'BV',1,'1'),
	   (3,'CX',2,'1+2'),
	   (4,'DSO',2,'1+2'),
	   (5,'ER',3,'1+2+3'),
	   (6,'ER',5,'1+2+3+5');
6 rows affected

SELECT *
FROM dbo.t;


W_ID
Title
Parent_id
View_Parent_id


1
AAA
null
null

2
BV
1
1

3
CX
2
1+2

4
DSO
2
1+2

5
ER
3
1+2+3

6
ER
5
1+2+3+5


with cte as (
      select w_id, parent_id, view_parent_id,
             0 as lev, convert(int, null) as parent, convert(varchar(max), view_parent_id + '+') as parents 
      from t
      union all
      select w_id, parent_id, view_parent_id,
             1 + lev, 
             convert(int, left(parents, charindex('+', parents) - 1)),
             stuff(parents, 1, charindex('+', parents), '')
      from cte
      where parents <> ''
     )
select w_id, parent_id, view_parent_id,
       max(case when lev = 1 then parent_title end) as title_1,
       max(case when lev = 2 then parent_title end) as title_2,
       max(case when lev = 3 then parent_title end) as title_3,
       max(case when lev = 4 then parent_title end) as title_4,
       max(case when lev = 5 then parent_title end) as title_5
from (select cte.*, t.title as parent_title, count(*) over (partition by cte.w_id) as cnt
      from cte join
           t
           on t.w_id = cte.parent
      where lev > 0
     ) cte
group by w_id, parent_id, view_parent_id;


w_id
parent_id
view_parent_id
title_1
title_2
title_3
title_4
title_5


2
1
1
AAA
null
null
null
null

3
2
1+2
AAA
BV
null
null
null

4
2
1+2
AAA
BV
null
null
null

5
3
1+2+3
AAA
BV
CX
null
null

6
5
1+2+3+5
AAA
BV
CX
ER
null


Warning: Null value is eliminated by an aggregate or other SET operation.


with cte as (
      select w_id, title, parent_id, view_parent_id,
             0 as lev, convert(int, null) as parent, convert(varchar(max), concat(view_parent_id, '+', w_id, '+')) as parents 
      from t
      union all
      select w_id, title, parent_id, view_parent_id,
             1 + lev, 
             convert(int, left(parents, charindex('+', parents) - 1)),
             stuff(parents, 1, charindex('+', parents), '')
      from cte
      where parents <> ''
     )
select w_id, title, parent_id, view_parent_id,
       max(case when lev = 1 then parent_title end) as title_1,
       max(case when lev = 2 then parent_title end) as title_2,
       max(case when lev = 3 then parent_title end) as title_3,
       max(case when lev = 4 then parent_title end) as title_4,
       max(case when lev = 5 then parent_title end) as title_5
from (select cte.*, t.title as parent_title, count(*) over (partition by cte.w_id) as cnt
      from cte join
           t
           on t.w_id = cte.parent
      where lev > 0
     ) cte
group by w_id, title, parent_id, view_parent_id;


w_id
title
parent_id
view_parent_id
title_1
title_2
title_3
title_4
title_5


1
AAA
null
null
null
AAA
null
null
null

2
BV
1
1
AAA
BV
null
null
null

3
CX
2
1+2
AAA
BV
CX
null
null

4
DSO
2
1+2
AAA
BV
DSO
null
null

5
ER
3
1+2+3
AAA
BV
CX
ER
null

6
ER
5
1+2+3+5
AAA
BV
CX
ER
ER


Warning: Null value is eliminated by an aggregate or other SET operation.
w_id	parent_id	view_parent_id	title_1	title_2	title_3	title_4	title_5
2	1	1	AAA	null	null	null	null
3	2	1+2	AAA	BV	null	null	null
4	2	1+2	AAA	BV	null	null	null
5	3	1+2+3	AAA	BV	CX	null	null
6	5	1+2+3+5	AAA	BV	CX	ER	null