Show Childs Inside the Parents and Sub-Childs Inside Their parent Child using Recursive Query in SQL Server.

Show Childs inside the Parents and sub-Childs inside their parent child using recursive query in SQL Server.

We have a table which contain following data…. Table= tbl_folder

Now We have write the SQL server Query to show Childs inside the Parents and sub-Childs inside their parent child

Query

WITH CategoryList

AS

(

SELECT parent.FolderId, CONVERT(VARCHAR(50), Parent.FolderName) as FolderName, parent.ParentfolderID

FROM tbl _folder as parent

WHERE parent.ParentfolderID =0

UNION ALL

SELECT child.Folderid, CONVERT(VARCHAR(50), CL.FolderName + ' / ' + child.FolderName) as FolderName, child.ParentfolderID

FROM tbl_folder as child

INNER JOIN CategoryList as CL ON child.ParentfolderID = CL.FolderId

WHERE child.ParentfolderID !=0

)

SELECT *

FROM CategoryList

After running above query the result will show as given:

Result

In the above example CategoryList is a Common Expression Table, the base record is derived by the first sql query before UNION ALL.

Second query after UNION ALL is executed repeatedly to get results and it will continue until it returns no rows. For above e.g. Result will have ParenfFolderId which have FolderId (ie, FolderId of the first result). This is obtained by joining table on columns FolderId with ParentFolderId of table Tbl_Folder.

WITH provides a way to write subqueries for use in a larger SELECT query. The subqueries, which are often referred to as Common Table Expressions or CTEs, can be thought of as defining temporary tables that exist just for this query. One use of this feature is to break down complicated queries into simpler parts

The general form of a recursive WITH query is always a non-recursive term, then UNION (or UNION ALL), then a recursive term, where only the recursive term can contain a reference to the query's own output. Such a query is executed as follows:

Recursive Query Evaluation

1. Evaluate the non-recursive term. For UNION (but not UNION ALL), discard duplicate rows. Include all remaining rows in the result of the recursive query, and also place them in a temporary working table.
2. So long as the working table is not empty, repeat these steps:
   
   • Evaluate the recursive term, substituting the current contents of the working table for the recursive self-reference. For UNION (but not UNION ALL), discard duplicate rows and rows that duplicate any previous result row. Include all remaining rows in the result of the recursive query, and also place them in a temporaryintermediate table.
   • Replace the contents of the working table with the contents of the intermediate table, then empty the intermediate table.

A useful property of WITH queries is that they are evaluated only once per execution of the parent query, even if they are referred to more than once by the parent query or sibling WITH queries. Thus, expensive calculations that are needed in multiple places can be placed within a WITH query to avoid redundant work. Another possible application is to prevent unwanted multiple evaluations of functions with side-effects. However, the other side of this coin is that the optimizer is less able to push restrictions from the parent query down into a WITH query than an ordinary sub-query. The WITH query will generally be evaluated as stated, without suppression of rows that the parent query might discard afterwards