Friday, 16 November 2012

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)        
    -- Anchor member definition        
    SELECT    cm.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,
            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 : 

Parent child hirarchy tree structure
(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.