SQL Server 2014

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

CREATE TABLE Table_Entity (
  id int,
  Level_1 Varchar(10),
  Level_2 Varchar(10),
  Level_3 Varchar(10),
  Level_4 Varchar(10)
);

INSERT INTO Table_Entity(id, Level_1, Level_2, Level_3, Level_4) VALUES 
 (1,'A1', null, null, null)
,(2, null, 'B1', null, null)
,(3, null, null, 'C1', null)
,(4, null, 'B2', null, null)
,(5, 'A2', null, null, null)
,(6, null, 'B3', null, null)
,(7, null, null, 'C2', null)
,(8, null, null, 'C3', null)
,(9, null, null, null, 'D1')
,(10, 'A3', null, null, null)
;

select * from table_Entity;

CREATE TABLE Table_Entity_OUTPUT (
  id int, 
  Level_1 Varchar(10),
  Level_2 Varchar(10),
  Level_3 Varchar(10),
  Level_4 Varchar(10)
);

INSERT INTO Table_Entity_OUTPUT(id, Level_1, Level_2, Level_3, Level_4) VALUES (1, 'A1', null, null, null);
INSERT INTO Table_Entity_OUTPUT(id, Level_1, Level_2, Level_3, Level_4) VALUES (2, 'A1', 'B1', null, null);
INSERT INTO Table_Entity_OUTPUT(id, Level_1, Level_2, Level_3, Level_4) VALUES (3, 'A1', 'B1', 'C1', null);
INSERT INTO Table_Entity_OUTPUT(id, Level_1, Level_2, Level_3, Level_4) VALUES (4, 'A1', 'B2', null, null);
INSERT INTO Table_Entity_OUTPUT(id, Level_1, Level_2, Level_3, Level_4) VALUES (5, 'A2', null, null, null);
INSERT INTO Table_Entity_OUTPUT(id, Level_1, Level_2, Level_3, Level_4) VALUES (6, 'A2', 'B3', null, null);
INSERT INTO Table_Entity_OUTPUT(id, Level_1, Level_2, Level_3, Level_4) VALUES (7, 'A2', 'B3', 'C2', null);
INSERT INTO Table_Entity_OUTPUT(id, Level_1, Level_2, Level_3, Level_4) VALUES (8, 'A2', 'B3', 'C3', null);
INSERT INTO Table_Entity_OUTPUT(id, Level_1, Level_2, Level_3, Level_4) VALUES (9, 'A2', 'B3', 'C3', 'D1');
INSERT INTO Table_Entity_OUTPUT(id, Level_1, Level_2, Level_3, Level_4) VALUES (10, 'A3', null, null, null);

id	Level_1	Level_2	Level_3	Level_4
1	A1	null	null	null
2	null	B1	null	null
3	null	null	C1	null
4	null	B2	null	null
5	A2	null	null	null
6	null	B3	null	null
7	null	null	C2	null
8	null	null	C3	null
9	null	null	null	D1
10	A3	null	null	null

id	Level_1	Level_2	Level_3	Level_4	rn	level_id	l1_row	l2_row	l3_row	L1_p	L2_p	L3_p	L1_h	L2_h	L3_h	Level_4
1	A1	null	null	null	1	1	1	1	1	1	1	1	A1	null	null	null
2	null	B1	null	null	2	2	0	2	2	1	2	2	A1	B1	null	null
3	null	null	C1	null	3	3	0	0	3	1	2	3	A1	B1	C1	null
4	null	B2	null	null	4	2	0	4	4	1	4	4	A1	B2	null	null
5	A2	null	null	null	5	1	5	5	5	5	5	5	A2	null	null	null
6	null	B3	null	null	6	2	0	6	6	5	6	6	A2	B3	null	null
7	null	null	C2	null	7	3	0	0	7	5	6	7	A2	B3	C2	null
8	null	null	C3	null	8	3	0	0	8	5	6	8	A2	B3	C3	null
9	null	null	null	D1	9	4	0	0	0	5	6	8	A2	B3	C3	D1
10	A3	null	null	null	10	1	10	10	10	10	10	10	A3	null	null	null