T-SQL: Hierarchical Table Sorting with a Parent-Child Relation
Problem
Given the following table Accounts
AccountID Name ParentID
--------- ---- --------
1 Alex 0
2 John 1
3 Mathew 2
4 Philip 1
5 Shone 0
6 Shine 2
7 Tom 2
8 George 1
9 Jim 5
the requirement is to have a query that should sort the table perfectly based on the Child parent hierarchy. Or more clearly each child must be directly under its parent. Like the below
AccountID Name ParentID
--------- ---- --------
1 Alex 0
8 George 1
2 John 1
3 Mathew 2
6 Shine 2
7 Tom 2
4 Philip 1
5 Shone 0
9 Jim 5
Think of it as a depth-first search, where the children are sorted in alphabetical order.
Go as far down the left-most branch as you can, then move one branch to the right. So the children of John have to be listed before carrying on listing the children of Alex
Solution
This uses a recursive cte to build the hierarchy, and each level, orders by name. If you leave the [path] column in the final select, you will see how it has been built up, and this is used to order the final result set.
declare @Accounts table (AccountID int, name varchar(50), ParentID int)
insert into @Accounts select 1,'Alex',0
insert into @Accounts select 2,'John',1
insert into @Accounts select 3,'Mathew',2
insert into @Accounts select 4,'Philip',1
insert into @Accounts select 5,'Shone',0
insert into @Accounts select 6,'Shine',2
insert into @Accounts select 7,'Tom',2
insert into @Accounts select 8,'George',1
insert into @Accounts select 9,'Jim',5
;with cte as
(
select
Accountid,
name,
parentid,
cast(row_number()over(partition by parentid order by name) as varchar(max)) as [path],
0 as level,
row_number()over(partition by parentid order by name) / power(10.0,0) as x
from @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)),
level+1,
x + row_number()over(partition by t.parentid order by t.name) / power(10.0,level+1)
from
cte
join @Accounts t on cte.AccountID = t.ParentID
)
select
Accountid,
name,
ParentID,
[path],
x
from cte
order by x
this gives
Accountid name ParentID path x
--------- --------- -------- ------ --------------------
1 Alex 0 1 1.000000000000000000
8 George 1 1-1 1.100000000000000000
2 John 1 1-2 1.200000000000000000
3 Mathew 2 1-2-1 1.210000000000000000
6 Shine 2 1-2-2 1.220000000000000000
7 Tom 2 1-2-3 1.230000000000000000
4 Philip 1 1-3 1.300000000000000000
5 Shone 0 2 2.000000000000000000
9 Jim 5 2-1 2.100000000000000000
The [path] column explains the level the account is in the hierarchy, so for example 'Shine' is 122, which reading right-to-left means the second child of the second child of the first child, or in other words, the second child of the second child of Alex => the second child of John.
See Also
- [[Transact-SQL Portal]]