2

In one of our databases we use a HierarchyId column. We then select the rows from the table like this:

SELECT HierarchyId.ToString()
FROM   Tree
ORDER BY HierarchyId DESC

This lays it out in a nice hierarchical structure but with all of the nodes in reverse order like this:

1/13/
1/12/123/
1/12/122/
1/12/121/
1/12/
1/11/
1/

However we would like the nodes to stay in this order but with the parent above the child nodes like this:

1/
1/13/
1/12/
1/12/123/
1/12/122/
1/12/121/
1/11/

The only solution that I can think of involves a recursive CTE which would negate a lot of the benefits of using HierarchyId. Does anyone have any ideas on how to do this?

Thanks,

Joe

JoeS
  • 1,405
  • 17
  • 30

1 Answers1

0

you can try this, hopefully it will work.

SELECT HierarchyId.ToString()
FROM   Tree
ORDER BY cast(HierarchyId as nvarchar(100)) DESC
Builder
  • 1,046
  • 2
  • 10
  • 30