CODEWITHSUNDEEP
sqlsql-server
StepUp
StepUp

Reputation: 38094

How to properly create a recursion query?

I have a table:

ItemID     ParentID             ItemName
  0          NULL                England
  50         0                   Hampshire county
  401        50                  Southampton
  402        50                  Portsmouth

What I want is the following table:

ItemID     Level        ItemName                ItemPath
  0          0           England                 England
  50         1           Hampshire county        England\HampshireCounty
  401        2           Southampton             England\HampshireCounty\Southampton
  402        2           Portsmouth              England\HampshireCounty\Portsmouth

I've created the following query:

WITH tree (A_ItemName, A_ItemId, A_Level, pathstr)
AS (SELECT ItemName, ItemId, 0, 
         CAST('' AS VARCHAR(MAX)) 
     FROM Items 
     WHERE ParentID IS NULL 
     UNION ALL 
     SELECT ItemName, ItemID, t.A_Level + 1, t.pathstr + '/' + V.ItemName
     FROM Items V 
         INNER JOIN tree t 
             ON t.A_ItemId = V.ItemID) 
SELECT SPACE(A_Level) + A_ItemName as A_ItemName, A_ItemId, A_Level, pathstr 
FROM tree 
ORDER BY pathstr, A_ItemId

However, I've got an error:

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

Any idea what I've done wrong?

Upvotes: 3

Views: 46

Answers (2)

Unnikrishnan R
Unnikrishnan R

Reputation: 5031

Use the below script for including the parent tag ('England').

WITH tree (A_ItemName, A_ItemId, A_Level, pathstr) AS (
      SELECT ItemName, ItemId, 0, CONVERT(VARCHAR(MAX),ItemName)
      FROM #t  
      WHERE ParentID IS NULL 
      UNION ALL 
      SELECT ItemName, ItemID, t.A_Level + 1, t.pathstr + '/' + V.ItemName
      FROM #t V INNER JOIN
           tree t 
           ON ISNULL(t.A_ItemId,0) = V.ParentId
     ) 
SELECT SPACE(A_Level) + A_ItemName as A_ItemName, A_ItemId, A_Level, pathstr 
FROM tree 
ORDER BY pathstr, A_ItemId

Upvotes: 2

Gordon Linoff
Gordon Linoff

Reputation: 1269443

You are missing the parent. I think this does what you want:

WITH tree (A_ItemName, A_ItemId, A_Level, pathstr) AS (
      SELECT ItemName, ItemId, 0, CAST(ItemName AS VARCHAR(MAX)) 
      FROM Items 
      WHERE ParentID IS NULL 
      UNION ALL 
      SELECT ItemName, ItemID, t.A_Level + 1, t.pathstr + '/' + V.ItemName
      FROM Items V INNER JOIN
           tree t 
           ON t.A_ItemId = V.ParentId
     ) 
SELECT SPACE(A_Level) + A_ItemName as A_ItemName, A_ItemId, A_Level, pathstr 
FROM tree 
ORDER BY pathstr, A_ItemId

Upvotes: 5

Related Questions