SQL Server recursive query

--sqlserver recursive query, mainly used to establish a tree structure if object_id (' Categories ') is not NULL DROP table categoriesgocreate table Categories (C     Ategoryid int, CategoryName VARCHAR (), ParentID int) Goinsert into Categories (CategoryID, CategoryName, ParentID) Select 1, ' Books ', NULL union ALL SELECT 2, ' SQL Server ', 1 UNION all select 3, ' ASP.    NET ', 1 union ALL Select 4, ' General ', 2 union ALL Select 5, ' SSIS ', 2 union ALL Select 6, ' TSQL ', 2 UNION ALL    Select 7, ' SSRS ', 2 union ALL Select 8, ' Beginners ', 4 union ALL Select 9, ' Certification Guide ', 4goWITH CTE as (  SELECT 0 as LVL, CategoryID, CategoryName, ParentID, CAST (CategoryID as VARCHAR) as Sort from Categories WHERE ParentID is a NULL UNION all SELECT p.lvl + 1, C.categoryid, C.categoryname, C.parentid, CAST (P.sort + ' /' + CAST (c.categoryid as varchar) as varchar) from Categories C INNER JOIN CTE p on P.categoryid = C.parentid) SELECT CategoryID, SPACE (LVL * 4) + CategorYname as CategoryName, sort, parentid from Cteorder by Sort 

Reproduced in http://beyondrelational.com/modules/2/blogs/28/posts/10486/recursive-cte-and-ordering-of-the-hierarchical-result.aspx

SQL Server recursive query