To get Tree Structure from Table using CTE in SQL Server

Here is an example to get hierarchy of categories example

DECLARE	@CATEGORIES	TABLE	
(
CATEGORYID INT,
CATEGORYNAME VARCHAR(100),
PARENTCATEGORYID INT
)
INSERT @CATEGORIES VALUES (1,'VEHICLE'	,NULL)
INSERT @CATEGORIES VALUES (2,'CAR'	,1)
INSERT @CATEGORIES VALUES (3,'BMW'	,2)
INSERT @CATEGORIES VALUES (4,'MERCEDES BENZ',2)
INSERT @CATEGORIES VALUES (5,'BIKES'	,1)
INSERT @CATEGORIES VALUES (6,'YAMAHA'	,5)
SELECT * 
FROM @CATEGORIES
/*
CATEGORYID CATEGORYNAME PARENTCATEGORYID
---------- ------------ -----------------
1          VEHICLE       NULL
2          CAR           1
3          BMW           2
4          MERCEDES BENZ 2
5          BIKES         1
6          YAMAHA        5
*/

;WITH CTE AS(
              SELECT CATEGORYID, 
                     CATEGORYNAME,
                     PARENTCATEGORYID, 
                     CONVERT(VARCHAR(MAX),CATEGORYNAME) TREE
               FROM  @CATEGORIES
               WHERE PARENTCATEGORYID IS NULL
               UNION ALL
               SELECT C.CATEGORYID, 
                      C.CATEGORYNAME, 
                      C.PARENTCATEGORYID, 
                      P.TREE + ' / ' + CONVERT(VARCHAR(MAX),C.CATEGORYNAME)
               FROM @CATEGORIES C
               JOIN CTE P
               ON C.PARENTCATEGORYID = P.CATEGORYID
               )
               SELECT * 
               FROM   CTE 
/*
CATEGORYID CATEGORYNAME PARENTCATEGORYID TREE
---------- ------------ ---------------- ----
1          VEHICLE       NULL            VEHICLE
2          CAR           1               VEHICLE / CAR
5          BIKES         1               VEHICLE / BIKES
6          YAMAHA        5               VEHICLE / BIKES / YAMAHA
3          BMW           2               VEHICLE / CAR / BMW
4          MERCEDES BENZ 2               VEHICLE / CAR / MERCEDES BENZ
*/