Hi to all, I have a tricky one (at least for me), I need to create a recursive query of the same table, let me explain a little more:


I have a table with these fields as structure and some sample data:
Category___Manufacturer____Brand________Weight_____Characteristic1_Char2___Char3______Char4_______Char5
Cereals_______Kellogs_________Corn Flakes__300 Gr_____Box_____________Natural_Corn_______No Def______No Def
Sodas_________Coca Cola Comp__Coca Cola____2000 Ml____Glass___________Cola____Light______No Def______No Def
Female Prot___Kimberly Klark__Kotex________12 Units___With Wings______Thin____with smell_Good Nights_No Def

The fields characteristic 1 to 5 could be different attributes for each category, like type of package, flavor, model, etc.

I have another table, with a field Character(6) that holds the order of the way that the user needs to see the information, I use six because Category, Manufacturer and Brand use the same order 0,1,2 respective.
The users decides the order of the other parts 0 for Weight, 1 for characteristic 1, 2 for characteristic 2 and so on.

I need to display a hierarchical view of the data depending of the order that the users decide, for example
>Weight
>>characteristic 1
>>>characteristic 2
>>>>characteristic 3
>>>>>characteristic 4
>>>>>>characteristic 5

but it could be :
>characteristic 1
>>Weight
>>>characteristic 5
>>>>characteristic 4
>>>>>characteristic 3
>>>>>>characteristic 2

it all depends of the order that the user decide, all the data to be displayed it's on the same table, so for each group I have to create a query with a more complex filter, the possible permutations of this are 720, I want to write an algorithm t use for N permutations, just in case one day they want to use more than 5 characteristics.

Any help as an start point, will be really appreciated.

Thanks in advance.

Luis Navas