I don't fully understand what you actually want here, it's always easier to figure out when you supply us with DDL, readily consumable sample data and expected results based on the sample data. Here's my shot in the dark.

First, this is the sample data I used to test with: -

-- Set up readily consumable sample data so that we can test any solutionIF object_id('tempdb..#testEnvironment') IS NOT NULLBEGIN; DROP TABLE #testEnvironment;END;

Cadavre (10/16/2013)I don't fully understand what you actually want here, it's always easier to figure out when you supply us with DDL, readily consumable sample data and expected results based on the sample data. Here's my shot in the dark.

First, this is the sample data I used to test with: -

-- Set up readily consumable sample data so that we can test any solutionIF object_id('tempdb..#testEnvironment') IS NOT NULLBEGIN; DROP TABLE #testEnvironment;END;

Sorry I know it's spoon feeding already. I've exerted all efforts but cant find the right script. I was thinking to put a "NULL" value to the columns that lacks so that i could be able to union. but the problem is i dont know where.

this is the part that i dont really understand. specially to the part where you put ".value". if you could walk me through on what it does that would be a GREAT HELP.

This way, you'd need to know the maximum number of levels for any car, and add Leveln= up to that level.

The alternative if you don't know the number of levels is that you could insert the unraveled hierarchy into a temp table and then use that along with some dynamic SQL to PIVOT the number of Level columns you need.

My advice:INDEXing a poor-performing query is like putting sugar on cat food. Yeah, it probably tastes better but are you sure you want to eat it?The path of least resistance can be a slippery slope. Take care that fixing your fixes of fixes doesn't snowball and end up costing you more than fixing the root cause would have in the first place.

My advice:INDEXing a poor-performing query is like putting sugar on cat food. Yeah, it probably tastes better but are you sure you want to eat it?The path of least resistance can be a slippery slope. Take care that fixing your fixes of fixes doesn't snowball and end up costing you more than fixing the root cause would have in the first place.

My advice:INDEXing a poor-performing query is like putting sugar on cat food. Yeah, it probably tastes better but are you sure you want to eat it?The path of least resistance can be a slippery slope. Take care that fixing your fixes of fixes doesn't snowball and end up costing you more than fixing the root cause would have in the first place.

cadavre i would really appreciate it if you can walk me through on your query. the stuff and xml path are all new to me.

Thanks a lot guys!!! Cheers!

Sorry, have been massively busy at work. Dwain's method for solving the issue of having different numbers of categories per car is the same that I'd been thinking of and the article he linked on creating a comma separated list will explain what I was doing with the FOR XML PATH stuff. If you need more help, post back.