0

I have this SQL Server table:

1

I want to execute a query that will return this output:

2

I tried to use the code from:

Multi-Level parent-child relationship

WITH CTE AS 
(
    SELECT A.[Value], A.[ValueID], 1 AS [Level]
    FROM [dbo].[MyTable] AS A
    WHERE A.ParentValueID = 0

    UNION ALL

    SELECT CTE.[Value], CTE.[ValueID], Level + 1 
    FROM CTE 
    INNER JOIN [dbo].[MyTable] AS B ON CTE.[ValueID] = B.[ValueID]
)
SELECT * FROM CTE

That did not seem to work as I got an error:

The statement terminated. The maximum recursion 100 has been exhausted before statement completion.

Blag
  • 5,818
  • 2
  • 22
  • 45
HelloPuppy
  • 81
  • 8
  • Hint: `OPTION (MAXRECURSION 0);` – Zhorov Nov 26 '21 at 09:23
  • Please don't post code and sample data/output/expected result as images. Please read [ask] in [help] for more details. – Pred Nov 26 '21 at 09:25
  • 1
    You either have a hierarchy with more than 100 levels, or you have a circular reference somewhere and it makes your recursion go forever in a loop (like A -> B -> C -> A) – Pred Nov 26 '21 at 09:27
  • Yeah, adding OPTION (MAXRECURSION 0) at the end loops forever still. – HelloPuppy Nov 26 '21 at 09:28
  • @Larnu This is more of a dupe of https://stackoverflow.com/questions/22452529/recursive-cte-in-presence-of-circular-references and not the one you choose. – Pred Nov 26 '21 at 09:31
  • Sounds like you have some circular references then, @HelloPuppy . – Thom A Nov 26 '21 at 09:31
  • @Pred sounds like there's a much larger problem, and the OP needs to actually supply consumable sample data and expected results, where we can run their above code and get the error they do. If they are getting that error for `OPTION (MAXRECURSION 0)` they must certainly have circular references, and there aren't any in that screenshot (which as mentioned, isn't particularly useful). – Thom A Nov 26 '21 at 09:33
  • 1
    I have reopened this, however, you need to provide a [mre] here, the sample data you've provided won't cause infinite recursion, and the said data isn't usable for those you are asking for help from. – Thom A Nov 26 '21 at 09:40
  • ok, got it, thank you for your inputs. – HelloPuppy Nov 26 '21 at 10:01

0 Answers0