Using the above sample data, you can unravel the hierarchy using a recursive CTE and then do a cross tab query to PIVOT.

WITH UnravelHiearchy AS

(

SELECT Level=0, Car, Child, Parent, Category_Name

FROM #TestEnvironment

WHERE Car IS NOT NULL

UNION ALL

SELECT Level+1, a.Car, b.Child, b.Parent, b.Category_Name

FROM UnravelHiearchy a

JOIN #TestEnvironment b ON a.Parent = b.Child

)

SELECT Car

,Level1=MAX(CASE WHEN Level = 1 THEN Category_Name END)

,Level2=MAX(CASE WHEN Level = 2 THEN Category_Name END)

,Level3=MAX(CASE WHEN Level = 3 THEN Category_Name END)

,Level4=MAX(CASE WHEN Level = 4 THEN Category_Name END)

,Level5=MAX(CASE WHEN Level = 5 THEN Category_Name END)

,Level6=MAX(CASE WHEN Level = 6 THEN Category_Name END)

FROM UnravelHiearchy

GROUP BY Car

ORDER BY Car;

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.

Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs [/url]

Edit: Sorry to be a bit redundant with what Cadavre posted. No coffee yet this morning so I didn't read it carefully.