You can use
CTE[
^] for such of requirement. See:
DECLARE @Subjects TABLE
(
SubjectId INT PRIMARY KEY IDENTITY(1,1),
SubjectText VARCHAR(70),
ParentSubjectId INT
);
INSERT INTO @Subjects (SubjectText, ParentSubjectId)
VALUES ('Accountancy',0),('Accounting',1),
('Bookkipping',1),('Payroll',1),
('Sage Payroll',4)
DECLARE @lastid INT
SELECT @lastid = MAX(ParentSubjectId) FROM @Subjects
SELECT @lastid = MAX(SubjectId) FROM @Subjects WHERE ParentSubjectId = @lastid
;WITH CTE AS
(
SELECT SubjectId, SubjectText, ParentSubjectId, CAST(SubjectText + ' > ' AS NVARCHAR(MAX)) AS LongSubject, 1 As LoopNo
FROM @Subjects
WHERE ParentSubjectId = 0
UNION ALL
SELECT s.SubjectId, s.SubjectText, s.ParentSubjectId, LongSubject + s.SubjectText + ' > ' AS LongSubject, LoopNo + 1 As LoopNo
FROM CTE AS c
INNER JOIN @Subjects As s ON c.SubjectId = s.ParentSubjectId
)
SELECT LongSubject, SubjectId, LoopNo AS DataLevel
FROM CTE
WHERE SubjectId = @lastid
Result:
LongSubject SubjectId DataLevel
Accountancy > Payroll > Sage Payroll > 5 3
Feel free to improve above code to your needs.