Dynamic join Hierarchical data

Question

i am researching about Hierarchical data by using ms sql .Here is my db value

Parent || Child
Fruit      Red
Red        Apple
Red        Strawberry
Fruit      Yellow
Yellow     banana
banana     papaya
Null       Fruit

How can i join the table to become something like this

Fruit
   Red
      Strawberry
      Apple
   Yellow
      banana
         papaya

It can be more nested . How can i dynamic join the table and show the result at above?

Answer 1

You should probably first investigate how you want to store the hierarchy before worrying how to query it (this will be somewhat dictated by your schema/storage structure). There are several common methods for storing category-type (i.e. hierarchical) data:

• Adjacency Lists
• Nested Sets
• Flat tables
• etc...

One of the better single sources of information I've seen is this page which has all the detail you could want, as well as links to examples, code samples, etc. Each has its own pros and cons regarding simplicity, performance, etc.

Cheers, Dave