Table with various subfleets

[himalayas]

I have a table with 4 columns: Fleetname, FleetId, ParentFleetId, VehicleId.
My Fleet has 16 Sub Fleets where Cipro is one of them. 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 and Procurement
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, plus all their Sub Fleets and their VehicleId's into a new table with the following columns and format.

Fleet1, FleetID1, Fleet2, FleetID2, Fleet3, FleetID3, Fleet4, FleetID4, VehicleId
-----------------------------------------------------------------------------------------------------------------
My Fleet, 9814, Null, Null, Null, Null, Null, Null, 120498
Null, Null, Cipro, 42263, Null, Null, Null, Null, 120409
Null, Null, Null, Null, Cipro sundry, 42264, Null, Null, 120097
Null, Null, Null, Null, Null, Null, Finance and Procurement, 43912, 120333


I guess I can use a cursor for this, but I want something that can automate the insert in table (dont want to query by FleetId, etc).
Can anyone help ?

[ck9663]

Try to gather all the records first then PIVOT/UNPIVOT your result set.

Happy Coding!!!

--- CK