yeah jeff as already answered to my similar query in here .

http://www.sqlservercentral.com/Forums/Topic1194903-338-1.aspx#bm1196064

but i can not reproduce in this example.

i think 5 element will be enough .

i tried this from the example didn't worked, because it give same hierarchypath to multiple rows.

here is what i tried.

DECLARE @Result TABLE (Serial nvarchar(10),Title varchar(20))

INSERT INTO @Result

SELECT '1.1','a' UNION ALL

SELECT '1.2.1','b' UNION ALL

SELECT '1.2.2','C' UNION ALL

SELECT '1.2.3','G' UNION ALL

SELECT '1.11','B' UNION ALL

SELECT '2.3','B' UNION ALL

SELECT '2.11','B' UNION ALL

SELECT '2.2','C' UNION ALL

SELECT '1.5','E' UNION ALL

SELECT '1.3','E'

;WITH cte

AS

(

SELECT *,CAST(CAST(serial AS BINARY(4)) AS VARBINARY(1000)) AS HierarchyPath

FROM @Result

)

,

cteSplit AS

(

SELECT CAST(SUBSTRING(h.HierarchyPath,(t.N*4)-3,4) AS INT) AS NewSerial,

h.title AS title,h.Serial

FROM dbo.Tally AS t WITH(NOLOCK)

CROSS JOIN cte AS h WITH(TABLOCKX)

WHERE t.N BETWEEN 1 AND DATALENGTH(h.HierarchyPath)/4

)

SELECT title,serial,newserial FROM cteSplit ORDER BY newserial

GO