SQL Server recursive query and SQL Server recursive query
-- SQL Server recursive query, mainly used to create a tree structure IF OBJECT_ID ('category') IS NOT NULL DROP TABLE CategoriesGOCREATE TABLE Categories (CategoryID INT, CategoryName VARCHAR (20), 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, 'cerication ication Guide', 4 goWITH cte AS (SELECT 0 AS lvl, CategoryID, CategoryName, parentID, CAST (CategoryID as varchar (128) AS Sort FROM Categories WHERE ParentID is null union all select p. lvl + 1, c. categoryID, c. categoryName, c. parentID, CAST (p. sort + '/' + CAST (c. categoryID as varchar) as varchar (128) 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
Reprinted on http://beyondrelational.com/modules/2/blogs/28/posts/10486/recursive-cte-and-ordering-of-the-hierarchical-result.aspx