Parent child Result from sql server?

Question

I have a category table as shown below:

 id title ParentID 1 C1 0 2 C2 0 3 C3 0 4 C4 2 5 C5 1 6 C6 3 7 C7 4 

I want a result like

 id title ParentID Level 1 C1 0 0 5 C5 1 1 2 C2 0 0 4 C4 2 1 7 C7 4 2 3 C3 0 0 6 C6 3 1 

How can I get this result?

Explanation of the order: I want a result with elements below their parent element.

Answer 1

This answer assumes that you are using SQL Server 2008 or later.

Use a recursive CTE and create an id string that you use in order as the hierarchy identifier .

 with C as ( select id, title, parentid, '/'+cast(id as varchar(max))+'/' as sort, 1 as lvl from YourTable where parentid = 0 union all select T.id, T.title, T.parentid, C.sort + cast(T.id as varchar(10)) + '/', C.lvl + 1 from YourTable as T inner join C on T.parentid = C.id ) select id, title, parentid, lvl, sort from C order by cast(sort as hierarchyid) 

SQL Fiddle

Answer 2

With a recursive CTE, which basically creates a materialized path from root elements to elements:

 ; WITH cte (id, title, parentID, path) AS ( SELECT id, title, parentID, CAST(RIGHT(REPLICATE('0',9) + CAST(id AS VARCHAR(10)) ,10) AS VARCHAR(MAX)) FROM category WHERE parentID = 0 UNION ALL SELECT a.id, a.title, a.parentID, CAST(b.path + RIGHT(REPLICATE('0',9) + CAST(a.id AS VARCHAR(10)) ,10) AS VARCHAR(MAX)) FROM category AS a JOIN cte AS b ON b.id = a.parentID ) SELECT id, title, parentID FROM cte ORDER BY path ; 

Test SQL script