How “deep” does your hierarchy go?...you have given just two levels.

If you have many more nested levels, such as this for example:

Shirts

..........Sports

....................Tennis

..............................Men

..............................Women

.....................Golf

..............................Men

..............................Women

...........Casual

Then I think it gets a little bit more difficult.

Take a look at this post and RBarryYoungs’ solution to a similar question here:

http://www.sqlservercentral.com/Forums/FindPost771406.aspx

regards gah