By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
438,413 Members | 1,594 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 438,413 IT Pros & Developers. It's quick & easy.

Table with various subfleets

P: 1
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 ?
Oct 1 '09 #1
Share this Question
Share on Google+
1 Reply

Expert 2.5K+
P: 2,878
Try to gather all the records first then PIVOT/UNPIVOT your result set.

Happy Coding!!!

--- CK
Oct 7 '09 #2

Post your reply

Sign in to post your reply or Sign up for a free account.