StepUp StepUp - 3 months ago 10
SQL Question

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?

Answer

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
Comments