views:

1181

answers:

3

Given that I have this resultset structure (superfluous fields have been stripped)

Id | ParentId | Name | Depth
----------------------------

is it possible to have the records returned in tree order i.e. Parent then Children, if a Child is a Parent, then their Children, if not then Sibling, etc? For example,

Id | ParentId | Name | Depth
----------------------------
1    NULL       Major    1
2    1          Minor    2
3    1          Minor    2
4    3          Build    3
5    3          Build    3
6    1          Minor    2

/* etc, etc */

The only way that I can think of doing this would be to follow this article -

Improve hierarchy performance using nested sets

and include [LeftExtent] and [RightExtent] fields against each record. Now the SQL in the article works fine when Ids are unique, but in this particular tree structure, a record with the same Id can appear in different places within the tree (the ParentId field is different, obviously). I think the problem is in this SQL from the article -

  INSERT INTO @tmpStack
    (
      EmployeeID, 
      LeftExtent
    )
  SELECT TOP 1 EmployeeID, @counter 
  FROM Employee 
  WHERE ISNULL(ParentID, 0) = ISNULL(@parentid,0) 
  /* If the Id has already been added then record is not given [LeftExtent] or [RightExtent] values. */
  AND EmployeeID NOT IN (SELECT EmployeeID FROM @tmpStack)

How can this be altered to allow records with duplicate Ids to be given [LeftExtent] and [RightExtent] values, or I am completely missing an easier way to return the resultset in the order that I require?

+1  A: 

You should have a look at recursive common table expressions in SQL Server 2005:

In your case, this would be something like:

WITH EmployeeCTE AS
(
   -- get the anchor
   SELECT ID, ParentID, Name, 0 as 'Depth'
   FROM Employee WHERE ParentID IS NULL

   -- recursively union lower levels
   UNION ALL
   SELECT e.ID, e.ParentID, e.Name, e.Depth+1
   FROM Employee e
   INNER JOIN EmployeeCTE ON e.ParentID = EmployeeCTE.ID
)
SELECT * FROM EmployeeCTE

This should give you a nice query result set with the data you're looking for. Or am I missing something?

Marc

marc_s
I have a CTE currently to recursively join records to build the resultset. This works fine, but the problem is that the order of records in the resultset is not in the order I would like as specified in the question. I'll amend the question to state resultset structure as opposed to table structure
Russ Cam
+1  A: 

Here's one that does the trick for me:

@ParentID is just a starting point in the hierarchy, but you can pass in 0 (but I think you're using null as the base ID, so you'll get the idea)

The key to ordered sorting is with the sort key that's built up.

WITH RoleHierarchy (RoleID, [Role], [Description], ParentID, Editable, HierarchyLevel, SortKey) AS
(
   -- Base
   SELECT
     RoleID,
     [Role],
     [Description],
     ParentID,
     Editable,
     0 as HierarchyLevel,
     CAST(RoleID AS VARBINARY(300))
   FROM
     dbo.Roles  
   WHERE
     RoleID = @ParentID

   UNION ALL

   -- Recursive
   SELECT
     e.RoleID,
     e.[Role],
     e.[Description],
     e.ParentID,
     e.Editable,
     th.HierarchyLevel + 1 AS HierarchyLevel,
     CAST (th.SortKey + CAST (e.[Role] AS VARBINARY(100)) AS VARBINARY(300))
   FROM
     Roles e
     INNER JOIN RoleHierarchy th ON e.ParentID = th.RoleID
    WHERE
     e.RoleID != 0
)

SELECT
    RoleID,
    ParentID,
    [Role],
    [Description],
    Editable,
    HierarchyLevel
FROM
    RoleHierarchy
WHERE
    RoleID != @ParentID
ORDER BY
    SortKey
ScottE
Tests on the resultset suggest this works well :)
Russ Cam
Well, let me know if you notice anything strange as I've been using this for a while!
ScottE
A: 

If you used materialized path or HIERARCHYID, you life would be much easier...

AlexKuznetsov
HIERARCHYID looks interesting, but alas, the solution is to work with SQL Server 2005
Russ Cam
Materialized path does work on 2005, and it can make your life much eaiser
AlexKuznetsov