Not a fair race!

You:

- Removed the tool tip column from your INSERTs

- Your INSERTs were missing tons of commas and quotes

- You changed the IDENTITY_INSERT setting

- I think you changed the name of the table too.

- And most of all you tried to throw me off by forgetting to include IDs 10 and 14!!!!

But nonetheless, as I said you needed to add an additional SMn for each level of depth you need to process. Hence my suggestion that you try a dynamic SQL solution (which I know you're good at so I won't presume to show you how).

;WITH ModuleHieararchy AS (

SELECT [SModuleId], [ModuleId], [SMName]

,[SDate]

,[EDate]

,[Status]

--,[ToolTip]

,[ParentId]

,n=1

,SM1=[SModuleId]

,SM2=[SModuleId]

,SM3=[SModuleId]

,Tree=CAST([SModuleID] AS VARCHAR(8000))

FROM ES_SubModuleMaster1

WHERE [ParentId] = 0

UNION ALL

SELECT a.[SModuleId], a.[ModuleId], a.[SMName]

,a.[SDate]

,a.[EDate]

,a.[Status]

--,a.[ToolTip]

,a.[ParentId]

,n+1

,SM1

,SM2=CASE n WHEN 1 THEN a.[SModuleId] ELSE SM2 END

,SM3=CASE WHEN n >= 1 THEN a.[SModuleId] ELSE SM3 END

,Tree + '/' + CAST(a.[SModuleID] AS VARCHAR(8000))

FROM ES_SubModuleMaster1 a

JOIN ModuleHieararchy b ON a.ParentID = b.sModuleID

)

SELECT [SModuleId], [ModuleId], [SMName]

,[SDate]

,[EDate]

,[Status]

--,[ToolTip]

,[ParentId]

,n,sm1,sm2

,Tree

FROM ModuleHieararchy

ORDER BY SM1, SM2, SM3, n