Skip to main content

Return to Question

edited tags
Link
John Cappelletti
John Cappelletti
  • 82.3k
  • 7
  • 50
  • 75
Source Link
Smiley
Smiley
  • 3.5k
  • 13
  • 51
  • 69

SQL Server - Order Data Hierarchy From Table

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!