case when parent = "0_" then _key else _parent end + convert(varchar(10),[ItemIndex]) as r1,I don't think this would work if you went more than 2 levels deep though.
>SELECT TOP 100 PERCENT > (rtrim([Parent]) + rtrim([_Key])) + convert(varchar(10),[ItemIndex]) as r1, > parent, > _key, > itemindex, > itemtext, > itemobject >FROM dbo.listbaritems >WHERE (itemform = 'ctreports') >ORDER BY 1 >>
>r1 parent _key itemindex itemtext >------------------------------------------------- >0_21_0 0_ 21_ 0 Schedule Reports >0_27_0 0_ 27_ 0 Case Reports >0_31_0 0_ 31_ 0 Procedure Reports >0_34_0 0_ 34_ 0 Surgeon Activity >0_41_0 0_ 41_ 0 Anesthetist Reports >0_43_0 0_ 43_ 0 Department Logs >0_47_0 0_ 47_ 0 Resource/Inventory >21_22_1 21_ 22_ 1 Scheduled Case Listing >21_23_2 21_ 23_ 2 Scheduler Activity Report >21_24_3 21_ 24_ 3 Block Utilization >21_26_4 21_ 26_ 4 Cancelled Case Listing >21_56_5 21_ 56_ 5 Staff Case Activity >21_57_6 21_ 57_ 6 Start Versus Actual >27_28_1 27_ 28_ 1 Pending Cases >27_29_2 27_ 29_ 2 Completed Cases >27_62_3 27_ 62_ 3 Case Activity Log >27_63_4 27_ 63_ 4 Room Utilization >27_65_5 27_ 65_ 5 Room Util. 1st Shift >30_32_1 30_ 32_ 1 Cost Report >30_32_2 30_ 32_ 2 Cost By Patient >30_33_3 30_ 33_ 3 Cost By Surgeon >30_54_4 30_ 54_ 4 Top Procedure Report >30_71_5 30_ 71_ 5 Cost By Payor >