What version of SQL Server are you using?

If you use a recursive CTE to traverse the tree then you can create the path from the root to specific node and then in the outer query use the offset window function LEAD to show the path of the next node in the sequence.

WITH Tree AS (

SELECT

student_id,

student_code,

student_parent_id,

student_name,

CAST(student_code AS varchar(MAX)) AS hierarchy,

CAST(student_id AS varbinary(900)) AS SortOrder

FROM

dbo.Student

WHERE

student_parent_id IS NULL

UNION ALL

SELECT

C.student_id,

C.student_code,

C.student_parent_id,

C.student_name,

P.hierarchy + '-' + C.student_code,

CAST(P.SortOrder + CAST(ROW_NUMBER() OVER(PARTITION BY C.student_parent_id ORDER BY C.student_id) AS binary(4)) AS varbinary(900))

FROM

Tree AS P

INNER JOIN

dbo.Student AS C

ON C.student_parent_id = P.student_id

)

SELECT

student_id,

student_code,

student_parent_id,

student_name,

hierarchy,

LEAD(hierarchy, 1, student_code) OVER(ORDER BY SortOrder) AS lead_hierarchy

FROM

Tree

ORDER BY

SortOrder;

GO

I am using the presentation order as the ordering subclause for the offset function since you did not provide any clue in the presence of siblings.

What should be the expected result if we add the following rows?

(5, '22', NULL, 'whatever-22'),

(6, '222', 5, 'whatever-222');

Should the output of the hierarchy for [student_id] = 4 be '11111' or '22'?