IF OBJECT_ID('tempdb..#test') IS NOT NULL

DROP TABLE #test

---- create table

create table #test(sid bigint, scode nvarchar(50), parentid bigint, sname nvarchar(50))

---- insert records

insert into #test values (1, '11', 0, 'a')

insert into #test values (2, '111', 1, 'b')

insert into #test values (3, '1111', 2, 'c')

insert into #test values (4, '11111', 3, 'd')

---- result query

;WITH SInfo AS

(

SELECT sId

,scode

,ParentId

,sName

,CONVERT(nvarchar(800),(scode+ '-' + scode+ '1')) AS Hierarchy

FROM #test

WHERE ParentId = 0

UNION ALL

SELECT TH.sId

,TH.scode

,TH.ParentId

,TH.sName

,CONVERT(nvarchar(800), (SInfo.Hierarchy +'-' + CONVERT(nvarchar(800), TH.scode) + CONVERT(nvarchar(800), 1)))

FROM #test TH

INNER JOIN SInfo ON SInfo.sId = TH.ParentId

)

Select * from SInfo