This code snippet works beautifully. Just replace the table/field names like so:
- AccountID with ID of your child record
- name with name of your child record
- parentID with the parent ID of of your child record
- @tbl_accounts with the name of your hierarchical table
Run and done:
with cte as
(
select
Accountid,
name,
parentid,
cast(row_number()over(partition by parentid order by name) as varchar(max)) as [path]
from @tbl_accounts
where parentid = 0
union all
select
t.AccountID,
t.name,
t.ParentID,
[path] + cast(row_number()over(partition by t.parentid order by t.name) as varchar(max))
from
30. cte
join @tbl_accounts t on cte.AccountID = t.ParentID
)
select
Accountid,
name,
ParentID,
[path]
from cte
order by path
Thanks, Kev Riley!
nerdjonistories 