This is more for fun than being an attempt to solve the problem, although the code can easily be amended to produce the desired results. It uses an inline Tally table and FOR XML to build the Hierarchy path.
😎
USE tempdb;
GO
SET NOCOUNT ON;
IF OBJECT_ID('dbo.test') IS NULL
BEGIN
---- create table
create table dbo.test(sid bigint, scode nvarchar(50), parentid bigint, sname nvarchar(50))
---- insert records
insert into dbo.test values
(1, '11', 0, 'a')
,(2, '111', 1, 'b')
,(3, '1111', 2, 'c')
,(4, '11111', 3, 'd');
END
--SET STATISTICS IO ON;
;WITH T(N) AS (SELECT N FROM ( VALUES (NULL),(NULL),(NULL),(NULL),(NULL)
,(NULL),(NULL),(NULL),(NULL),(NULL)) AS X(N))
--sId scode ParentID sName Hierarchy
SELECT
T.sid
,T.scode
,T.parentid
,T.sname
,(( SELECT
TP.scode + '\'
FROM dbo.test TT
OUTER APPLY
(
SELECT TOP((TT.parentid) + 1) ROW_NUMBER() OVER (ORDER BY (SELECT NULL)) -1 AS N FROM
T T1,T T2,T T3,T T4,T T5,T T6,T T7
) AS NM(N)
LEFT OUTER JOIN dbo.test TP
ON NM.N = TP.sid
WHERE T.sid = TT.sid
FOR XML PATH(''),TYPE).value('.[1]','VARCHAR(MAX)') + T.scode
) AS Hierarchy
FROM dbo.test T
--SET STATISTICS IO OFF;
Results
sid scode parentid sname Hierarchy
---- ------ --------- ------ ------------------
1 11 0 a 11
2 111 1 b 11\111
3 1111 2 c 11\111\1111
4 11111 3 d 11\111\1111\11111