SQL Server Script : Get Parent Child hierarchy or tree structure or recursive records in sql query

You can get parent child hierarchy records or category sub category records or recursive records from table in sql server and display as a tree structure.
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.)

category_id	category_name	parent_category_id
1	animal	NULL
2	vegetable	NULL
3	fruit	NULL
4	doggie	1
6	horsie	1
8	birdie	1
9	carrot	2
10	leafy	2
11	potato	2
12	celery	10
13	rutabaga	2
14	mango	3
15	orange	3
16	graps	3
17	apple	3
19	companion	4
20	herding	4
24	poodle	19
25	chihuahua	19
26	shepherd	20
27	collie	20

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

Output : 

(To view original image , click on image)

This is the very useful SQL Server Scripts.

Note : Give Us your valuable feedback in comments. Give your suggestions in this article so we can update our articles accordingly that.