Get more than 1 result set for a recursive CTE?

Question

I have a simple table that has information about leaves and sites. (e.g. forum questions)

The main message is childId where childId and ParentID are the same

So, we see two main questions and their answers.

I also managed to calculate the depth of each element:

In short, this is the main request:

  WITH CTE AS ( SELECT childID ,parentID, 0 AS depth,name FROM @myTable WHERE childID = parentID AND childID=1 -- problem line UNION ALL SELECT TBL.childID ,TBL.parentID, CTE.depth + 1 , TBL.name FROM @myTable AS TBL INNER JOIN CTE ON TBL.parentID = CTE.childID WHERE TBL.childID<>TBL.parentID ) SELECT childID,parentID,REPLICATE('----', depth) + name 

But the problem is line 8 (commented out).

I am currently asking "give me the whole cluster for question id # 1"

So where is the problem?

I want to have a multiple set for each question!

so here I need to have 2 result sets:

one for childId=parentId=1 and one for one for childId=parentId=6

full working sql online

(and I do not want to use the cursor)

Answer 1

To present multiple result sets for your client, you will have to use a cursor or while loop to perform independent SELECT operations. You cannot do this from a CTE, since a CTE can only be used with one subsequent request.

Now the source of the problem has nothing to do with cursors, but the fact that you are using an HTML repeater. Why do you need to use an HTML repeater for this? A simple DataReader can go through all the results from one set of CTEs and make conditional loop-based formatting decisions and determine when the root identifier changes. Therefore, I suggest that you look at the solution to the presentation problem differently, instead of trying to force SQL Server to implement your presentation.

Answer 2

You can create your queries dynamically.

 DECLARE @SQL NVARCHAR(MAX) = (SELECT ' WITH CTE AS ( SELECT childID ,parentID, 0 AS depth,name FROM myTable WHERE childID = parentID AND childID = '+CAST(childID AS NVARCHAR(10))+' UNION ALL SELECT TBL.childID ,TBL.parentID, CTE.depth + 1 , TBL.name FROM myTable AS TBL INNER JOIN CTE ON TBL.parentID = CTE.childID WHERE TBL.childID<>TBL.parentID ) SELECT childID,parentID,REPLICATE(''----'', depth) + name FROM CTE ORDER BY childID;' FROM myTable WHERE childID = parentID FOR XML PATH(''), TYPE).value('text()[1]', 'NVARCHAR(MAX)'); EXEC sp_executesql @SQL; 

Update:

As suggested by Bogdan Sahlei, we can minimize compilation by making a parameterized actual query.

 DECLARE @SQL1 NVARCHAR(MAX) = 'WITH CTE AS ( SELECT childID ,parentID, 0 AS depth,name FROM myTable WHERE childID = parentID AND childID = @childID UNION ALL SELECT TBL.childID ,TBL.parentID, CTE.depth + 1 , TBL.name FROM myTable AS TBL INNER JOIN CTE ON TBL.parentID = CTE.childID WHERE TBL.childID<>TBL.parentID ) SELECT childID,parentID,REPLICATE(''----'', depth) + name FROM CTE ORDER BY childID;' DECLARE @SQL2 NVARCHAR(MAX) = (SELECT 'exec sp_executesql @SQL, N''@childID int'', '+CAST(childID AS NVARCHAR(10))+';' FROM myTable WHERE childID = parentID FOR XML PATH(''), TYPE).value('text()[1]', 'NVARCHAR(MAX)'); EXEC sp_executesql @SQL2, N'@SQL NVARCHAR(MAX)', @SQL1; 

Answer 3

I'm not sure what you mean by "returning two sets of results." You can have only one set of results, and the root of the question will be assigned to another column? The following setting in your request does the following:

 WITH CTE AS ( SELECT ChildId as WhichQuestion, childID, parentID, 0 AS depth, name FROM @myTable WHERE childID = parentID UNION ALL SELECT cte.WhichQuestion, TBL.childID, TBL.parentID, CTE.depth + 1 , TBL.name FROM @myTable AS TBL INNER JOIN CTE ON TBL.parentID = CTE.childID WHERE TBL.childID <> TBL.parentID ) SELECT WhichQuestion, childID, parentID, REPLICATE('----', depth) + name FROM CTE ORDER BY WhichQuestion, childID; 

Answer 4

You can save childId from the root question in the anchor part, and then access the entire branch using Id:

 WITH CTE AS ( SELECT childID as RootId , childID , parentID , 0 AS depth,name FROM @myTable WHERE childID = parentID UNION ALL SELECT CTE.RootId , TBL.childID , TBL.parentID , CTE.depth + 1 , TBL.name FROM @myTable AS TBL INNER JOIN CTE ON TBL.parentID = CTE.childID WHERE TBL.childID<>TBL.parentID )