• 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