Allow me to make amends for my prior transgression with my take on the Dynamic SQL version:

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 Level, Car, Child, Parent, Category_Name

INTO #Temp

FROM UnravelHiearchy;

DECLARE @SQL NVARCHAR(MAX);

SELECT @SQL = '

SELECT Car' +

(

SELECT ' ,Level' + CAST(n AS VARCHAR) + '=MAX(CASE WHEN Level = ' +

CAST(n AS VARCHAR) + ' THEN Category_name END)'

FROM

(

SELECT TOP

(

(SELECT TOP 1 COUNT(*) FROM #Temp GROUP BY Car ORDER BY 1 DESC) - 1

) ROW_NUMBER() OVER (ORDER BY (SELECT NULL)) FROM sys.all_columns

) a(n)

ORDER BY n

FOR XML PATH(''), TYPE).value('.', 'VARCHAR(MAX)'

)

+ ' FROM #Temp

GROUP BY Car

ORDER BY Car;'

--PRINT @SQL;

EXEC (@SQL);

GO

DROP TABLE #Temp;