Parent Child Result from sql server? [duplicate]

Question

This question already has answers here:

How to get result from parent child table (4 answers)

Closed 7 years ago.

I have a table category as 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 result as

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 result with items below their parent item.

Answer 1

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

Use a recursive CTE and build a string of id's that you use in the order by as a hierarchy id.

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 builds the materialized path from root items to items:

; 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 at SQL-Fiddle