Don't know if this is the quickest/best way, but here is one way. I can't help thinking that there is a simpler way. The code below parses, but I have not run it on actual data. So if you choose to use it, run the select statement (as I have it now) to see if Lineage2 is what you are looking for. If that looks correct, uncomment the update statement and remove the select and run to do the update.with cte1 as(select *, row_number() over (order by level) RNfrom Hierarchy),cte2 as( select *, cast('\'+ right('000000'+cast(RN as varchar(32)),6) as varchar(max)) as Lineage2 from cte1 where Level = 1 union all select c1.*, c2.Lineage2+cast('\'+ right('000000'+cast(c1.RN as varchar(32)),6) as varchar(max)) from cte2 c2 inner join cte1 c1 on c2.Node = c1.ParentNode)-- update cte2 set Lineage = Lineage2;select * from cte2; 
Replace Hierachy text node with IDs
Posted - 2011-07-02 : 04:23:39
