CODEWITHSUNDEEP
sqlrecursioncommon-table-expression
Nick77
Nick77

Reputation: 23

Recursive CTE SQL Get all Levels

I have to create a sql query with a recursive CTE and I don't know how to proceed.

My table has many entries which all have an Id and a parentId.

I want to get a structure like this:

And so on.....

All entries which have a parent should be listed under the parent. ( My table has 3 - 5 Levels, I am not sure at the moment.)

My recent attempt looks like this:

WITH Query AS 
( 
SELECT P.Name, P.Id, P.ParentId
FROM Table P 

UNION ALL

SELECT P1.Name, P1.Id, P1.ParentId
FROM Table P1
INNER JOIN Query M ON M.ParentId = P1.Id
)
SELECT * FROM Query

I had tried different thigs with CTE but didnt reached my goal. I worked many hours on this problem and don't know how to procced. I hope someone could help me. Thanks in advance.

I just want every entry with a ParentId on the top and all the child items under it. Multiple Entries are ok.

Upvotes: 1

Views: 2885

Answers (1)

shibormot
shibormot

Reputation: 1638

;WITH Query AS 
( 
SELECT P.Name, P.Id, P.ParentId
FROM Table P 
where P.ParentId = 0 -- assume root level records have ParentId = 0

UNION ALL

SELECT C.Name, C.Id, C.ParentId
FROM Table C
INNER JOIN Query P ON P.Id = C.ParentId
)
SELECT * FROM Query
option ( MaxRecursion 0 );

Upvotes: 2

Related Questions