USING RECURSIVE COMMON TABLE EXPRESSIONS TO REPRESENT TREE STRUCTURES

Problem : 
Suppose if you have table structure which contains reference as same table for parent entry and you want to display data in table structure then below query will be useful.

Table Structure is:-
catid       catname                parentcatid
1             Cat1                     0
2             Cat2                     0
3             Cat3                     0
4             Cat1_sub1            1
5             Cat1_sub2            1
6             Cat1sub_sub         4
7             Cat2_sub1            2
8             Cat3_sub1            3
9             Cat2_subsub         7
10           Cat2_subsub         6

Desired output is:
Parentcatid                   Tree_Structure
0                                  Cat1
1                                  Cat1->Cat1_sub1
4                                  Cat1->Cat1_sub1->Cat1sub_sub
6                                  Cat1->Cat1_sub1->Cat1sub_sub->Cat2_subsub
1                                  Cat1->Cat1_sub2
0                                  Cat2
2                                  Cat2->Cat2_sub1
7                                  Cat2->Cat2_sub1->Cat2_subsub
0                                  Cat3
3                                  Cat3->Cat3_sub1

SQL Query:-

WITH supplytree AS
(SELECT catid, catname, parentcatid, CAST(catname As varchar(1000)) As si_item_fullname
FROM #tempsample
WHERE parentcatid = 0 
UNION ALL
SELECT si.catid,si.catname,
si.parentcatid,
CAST(sp.si_item_fullname + '->' + si.catname As varchar(1000)) As si_item_fullname
FROM #tempsample As si
INNER JOIN supplytree AS sp
ON (si.parentcatid = sp.catid)
)
SELECT parentcatid, si_item_fullname
FROM supplytree
ORDER BY si_item_fullname;