Post reply

Find all the vehicleid's of top tree and all the subtrees of each subtree

  • October 4, 2009 at 6:01 am

    #136253

    I have a table with 3 columns: Fleetname, FleetId, ParentFleetId.

    Another table with column: VehicleId. I use a join to get all 4 columns.

    My Fleet is top of tree, has 16 Sub Fleets (sub trees) where Cipro is one of them.

    Below I listed the Sub Fleets of Cipro as well (Cipro sundry, Finance and Procurement).

    Fleet Name: My Fleet

    Fleet ID: 9814

    Sub Fleet1 (below My Fleet)

    Fleet Name: Cipro

    Fleet ID: 42263

    ParentFleetId: 9814

    Sub Fleet2 (below Cipro)

    Fleet Name: Cipro sundry

    Fleet ID: 42264

    Sub Fleet3 (below Cipro sundry)

    Fleet Name: Finance

    Fleet ID: 43912

    How can I get all of the VehicleId's of My Fleet,

    each of the 16 Sub Fleets of My Fleet and all their VehicleId's,

    all the Sub Fleets of the 16 Sub Fleets of My Fleet and their VehicleId's

    into a table with the following columns:

    Fleet1, FleetID1, Fleet2, FleetID2, Fleet3, FleetID3, Fleet4, FleetID4, VehicleId.

    Below is an example:

    My Fleet, 9814, Null, Null, Null, Null, Null, Null, 120498 -- top of tree

    Null, Null, Cipro, 42263, Null, Null, Null, Null, 120409 -- Cipro - 1st subtree

    Null, Null, Null, Null, Cipro sundry, 42264, Null, Null, 120097 -- Cipro sundry - subtree of Cipro

    Null, Null, Null, Null, Null, Null, Finance, 43912, 120333 -- Finance - subtree of Cipro sundry

    Null, Null, Health, 42270, Null, Null, Null, Null, 120433 -- Health - 2nd subtree

    Null, Null, Null, Null, Health sundry, 42273, Null, Null, 120111 -- Health sundry - subtree of Health

    Null, Null, Null, Null, Null, Null, Membership, 45109, 120511 -- Membership - subtree of Health sundry

    I guess I can use a cursor or rownumber for this, whatever I use I require something

    that can automate the insert into a temp table.

  • October 4, 2009 at 2:19 pm

    #1061494

    Please read and follow the recommendation shown in the link in my signature.

    Your sample data seem to be incomplete:

    - Values for ParentFleetID are missing

    - There is no relation shown between VehicleID and FleetID

    - Your expected result doesn't match the sample data you provided.

    Another option is to look for recursive CTE's on this site to find some solutions.


    Lutz
    A pessimist is an optimist with experience.

    How to get fast answers to your question[/url]
    How to post performance related questions[/url]
    Links for Tally Table [/url] , Cross Tabs [/url] and Dynamic Cross Tabs [/url], Delimited Split Function[/url]

Viewing 2 posts - 1 through 1 (of 1 total)

You must be logged in to reply to this topic. Login to reply