Sql hierarchy ID Sort by level

Question

Is it possible to sort sql data in a hierarchy using the hierarchy identifier, and then for each level sort in alphabetical order?

So, we have a staff table listing organizational hierarchy based on employee ID

You have Bob (5), in whom Phil (17) and Charlie (28) tell him, and Josie (6) has Tyler (15) and Mike (56), telling her.

If you sort it by HierarchyID, it will look like this:

Bob (/ 5 /)
--Phil (/ 5/17 /)
--Charlie (/ 5/28 /)
Josie (/ 6 /)
--Tiler (/ 6/15 /)
--Mike (/ 6/56 /)

But it would probably be wiser to make it look like

Bean
--Charlie
--Phil
Josie
--Mike
--Tyler

Is this possible if he is too confused?

Answer 1

- Will it work? this is taken from Sort tree with another column in SQL Server 2008

DECLARE @table TABLE (id INT NOT NULL PRIMARY KEY, name NVARCHAR(4000) NOT NULL, path HIERARCHYID) INSERT INTO @table VALUES (1, 'People', '/'), (2, 'Girls', '/1/'), (3, 'Boys', '/2/'), (4, 'Zoey', '/1/1/'), (5, 'Kate', '/1/2/'), (6, 'Monica', '/1/3/'), (7, 'Mark', '/2/1/'), (8, 'David', '/2/2/') ;WITH q AS ( SELECT *, HIERARCHYID::Parse('/') AS newpath FROM @table WHERE path = HIERARCHYID::GetRoot() UNION ALL SELECT t.*, HIERARCHYID::Parse(q.newpath.ToString() + CAST(ROW_NUMBER() OVER (ORDER BY t.name) AS NVARCHAR(MAX)) + '/') FROM q JOIN @table t ON t.path.IsDescendantOf(q.path) = 1 AND t.path.GetLevel() = q.path.GetLevel() + 1 ) SELECT replicate(convert(nvarchar, '-'), q.path.GetLevel()) + q.name /*+ '(' + q.newpath.ToString() + ')'*/ FROM q ORDER BY newpath 

Answer 2

This is the solution I found

 declare @oldId hierarchyid, @newId hierarchyid, @parent hierarchyid select @oldId = id_node, @parent = id_node.GetAncestor(1) from gbs.T_Hierarchy_Activities ha where ID = @ID_Object select @newId = @oldId.GetReparentedValue(@oldId, ID_Node) from ( select row_number() over(order by id_node) rn, id_node from gbs.T_Hierarchy_Activities ha cross join (select * from common.FX_Permissions() where ID_Feature = 10) p where ID_Node.IsDescendantOf(@oldId.GetAncestor(1)) = 1 and ID_Level = @oldId.GetLevel() and ha.ID_Office = p.ID_Office ) a where rn = @NewPosition update gbs.T_Hierarchy_Activities set ID_Node = case when ID_Node = @oldId then @newId else @oldId end where ID_Node in (@oldId, @newId) 

Answer 3

If I don’t understand something, you can simply add a secondary sort field to the ORDER BY . For instance:

 SELECT * FROM Employees ORDER BY HierarchyID, Name