Please help

[Praveen Pandit]

Hi,

I have requirement to create an output which should show the result in a hierarchical tree structure.

Below is the requirement.

I have table called dealers in which there are multiple columns, out of which I need to use the below columns to generate a hierarchical tree for generating a sales report for every month.

dealer_id
url
dlr_type
fk_parent_dealerid


1. "dlr_type" can contain the types like "GROUP", "DEALER".
2. For the Main Group there can be any number of sub-groups with the same dlr_type "GROUP"

3. For the dlr_type "GROUP" the fk_parent_dealerid is always NULL for the top most "GROUP" (for the main Group) in the hierarchy.

Example:

dealerid, url, dlr_type, fk_parent_dealerid
----------- ------------- -------------- ---------------------
102863, TGA, GROUP, (null)



4. For the next level, in the Main Group dealerid 102863 will be the fk_parent_dealerid

Example:

select dealerid, websiteurl, dealer_type, fk_parent_dealerid from dealers where dealerid = 102863;

The above query can give result like

dealerid, url, dlr_type, fk_parent_dealerid
----------- ------------------- -------------- ---------------------
102864, DITROEN SAVEL OUEST, GROUP, 102863
102876, DITROEN SAVEL EST, GROUP, 102863


Which are sub-groups for the main Group "TGA".

Similarly, if I use the dealerid 102864 from the above result it can display the next level hierarchy which can be a sub-group itself or a "DEALER".

When the dlr_type is "DEALER" that means that is the final node in the hierarchy which can have any number of elements starting from 1 to any number.

Example output with the final node elements with thr dlr_type = 'DEALER':

dealerid, url, dlr_type, fk_parent_dealerid
----------- ---------------------------- -------------- ---------------------
102866, dev.test.fr, DEALER, 102865
102867, dev.test1.fr, DEALER, 102865


Can anyone help in writing a SQL or stored procedure to generate the output?


Thanks & regards
P.Pandit