We are using A common table expression (CTE) to get result. In fact we are using Recursive CTE to get this recursive data.
Here is example for this.
In this example we take one table "category_master". This table contains recursive records. This table has "category_id" , "category_name" and "parent_category_id" columns. "parent_category_id" column contain reference of "category_id". We can insert "N - Level" of parent child relationship records. We retrieve those records using recursive "CTE" and we can also do Hierarchy level by applying spacing so it's look like tree structure.
Table "category_master" records. (Download category_master.csv data file.)
SQL Query :
WITH DirectReports (ID, PARENT_NAMEID,HLevel,NAME,Displayname) AS ( -- Anchor member definition SELECT cm.category_id, cd.parent_category_id, 0 as HLevel, cast(cm.category_name as varchar(max)) as NAME, cast(Right(10000 + (cast('0' as varchar(max)) + cast(cm.category_id as varchar(max))),4) as varchar(max)) as Displayname FROM category_master cm INNER JOIN category_master cd ON cm.category_id = cd.category_id WHERE cm.parent_category_id IS NULL UNION ALL -- Recursive member definition SELECT cm.category_id, cd.parent_category_id, HLevel+1, cast((RIGHT(' ',(HLEVEL + 1) * 4) + isnull(cm.category_name,'')) as varchar(max)) as NAME, cast(cast(Displayname as varchar(max)) + cast(cm.category_id as varchar(max)) as varchar(max)) as Displayname FROM category_master cm INNER JOIN category_master cd on cm.category_id = cd.category_id INNER JOIN DirectReports d on cd.parent_category_id = d.ID ) -- Statement that executes the CTE SELECT ID, PARENT_NAMEID,HLevel,[NAME],Displayname from DirectReports ORDER BY Displayname
|(To view original image , click on image)|
Note : Give Us your valuable feedback in comments. Give your suggestions in this article so we can update our articles accordingly that.