SQL Server - Order Data Hierarchy From Table

Question

I have a table with the following structure:

|  ObjectID  |  ParentID |  Description  |  Level  |
----------------------------------------------------
|  1000      |  NULL     |  Food         |  0      |
|  1001      |  1000     |  Fruit        |  1      |
|  1002      |  1000     |  Vegetable    |  1      |
|  1003      |  1001     |  Apple        |  2      |
|  1004      |  1001     |  Banana       |  2      |
|  1005      |  1002     |  Cabbage      |  2      |
|  1006      |  1002     |  Spinach      |  2      |
|  1007      |  1003     |  Red          |  3      |
|  1008      |  1003     |  Green        |  3      |
|  1009      |  1007     |  Single       |  4      |
|  1010      |  1007     |  Bunch        |  4      |
|  1011      |  1010     |  Organic      |  5      |
|  1012      |  1010     |  Non-Organic  |  5      |

It basically lists a bunch of objects with hierarchy in a single table. Now I need to be able to query this table and come up with a hierarchy based of a single ObjectID. Like so:

In this example, I need to grab everything under the 'Apple' Hierarchy so that the resulting set would be:

|  ObjectID  |  ParentID |  Description  |  Level  |
----------------------------------------------------
|  1003      |  1001     |  Apple        |  2      |
|  1007      |  1003     |  Red          |  3      |
|  1009      |  1007     |  Single       |  4      |
|  1010      |  1007     |  Bunch        |  4      |
|  1011      |  1010     |  Organic      |  5      |
|  1012      |  1010     |  Non-Organic  |  5      |
|  1008      |  1003     |  Green        |  3      |

Notice how this orders the rows by Level wherein the immediate child comes after the parent.

I would really appreciate the help! Thanks!

Possible duplicate of [SQL Server recursive query](https://stackoverflow.com/questions/3916597/sql-server-recursive-query) — Tab Alleman, Jun 13 '17 at 18:59
This duplicate shows you how to include the Level in the results: https://stackoverflow.com/questions/14274942/sql-server-cte-and-recursion-example — Tab Alleman, Jun 13 '17 at 19:01

John Cappelletti · Accepted Answer · 2017-06-13T19:36:03.827

Declare @Top int = 1003  --<<  Set To NULL for Full Hier

;with cteP as (
      Select [ObjectID]
            ,[ParentId]
            ,[Level]
            ,[Description] 
            ,[Sequence]  = cast(10000+Row_Number() over (Order by [ObjectID]) as varchar(500))
      From   @YourTable 
      Where  IsNull(@Top,-1) = case when @Top is null then isnull([ParentId] ,-1) else [ObjectID] end
      Union  All
      Select r.[ObjectID]
            ,r.[ParentId]
            ,r.[Level]
            ,r.[Description] 
            ,cast(concat(p.[Sequence],'.',10000+Row_Number() over (Order by r.[ObjectID])) as varchar(500))
      From   @YourTable r
      Join   cteP p on r.[ParentId]  = p.[ObjectID])
Select [ObjectID]
      ,[ParentId]
      ,[Description]
      ,[Level]
 From cteP 
 Order By [Sequence]

Returns

ObjectID    ParentId    Description Level
1003        1001        Apple       2
1007        1003        Red         3
1009        1007        Single      4
1010        1007        Bunch       4
1011        1010        Organic     5
1012        1010        Non-Organic 5
1008        1003        Green       3

Thanks so much! The added bit to show the nesting for readability is awesome! — Smiley, Jun 13 '17 at 21:08
@Smiley I suspect you already got that then. I edited to mirror your desired results. Happy it helped. — John Cappelletti, Jun 13 '17 at 21:15

score 0 · Answer 2 · answered Jun 13 '17 at 18:52

Follow the Example in Books Online:

https://learn.microsoft.com/en-us/sql/t-sql/queries/with-common-table-expression-transact-sql

USE AdventureWorks2012;  
GO  
WITH DirectReports(ManagerID, EmployeeID, Title, EmployeeLevel) AS   
(  
    SELECT ManagerID, EmployeeID, Title, 0 AS EmployeeLevel  
    FROM dbo.MyEmployees   
    WHERE ManagerID IS NULL  
    UNION ALL  
    SELECT e.ManagerID, e.EmployeeID, e.Title, EmployeeLevel + 1  
    FROM dbo.MyEmployees AS e  
        INNER JOIN DirectReports AS d  
        ON e.ManagerID = d.EmployeeID   
)  
SELECT ManagerID, EmployeeID, Title, EmployeeLevel   
FROM DirectReports  
ORDER BY ManagerID;  
GO

SQL Server - Order Data Hierarchy From Table

2 Answers2