This question already has answers here:
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.
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
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
