Retrieving Hierarchical Database table using Recursive CTE

Read Previous article: Hierarchical Data Storing in Database table

Problem Statement:

Suppose folder structure is der like folder n subfolder so suppose “Main” folder has sub folder “A” and “B” and “A”  has “a1” and B has “b1” so how will you maintain dis data in single table and how you will get all sub folders(“A”,”B”,”a1”,”b1”) of “Main” folder .

Level1      Level2      Level3

---------- ---------- ----------

mail       A          a1

Solution :

create table SnehalKaQuestion

(FolderId int,

FolderName varchar(10),

ParentFolder int null) 

insert into SnehalKaQuestion values(1,'mail',null),(2,'A',1),(3,'a1',2),(4,'B',1),(5,'b1',4)

select * from SnehalKaQuestion

FolderId    FolderName ParentFolder

----------- ---------- ------------

1           mail       NULL

2           A          1

3           a1         2

4           B          1

5           b1         4

(5 row(s) affected)

Now we want to find the level of each folder in the folder hierarchal structure, lets see how we will be achieving this use recursive CTE.

Here we are creating a stored procedure which will use CTE to derive the required result

create procedure proc_FindFolderLevel

@folderName varchar(10)

as

begin

;with cte(folderid,foldername,parentfolder)

as

(

select folderid,foldername,parentfolder from SnehalKaQuestion where foldername = @folderName

union all

select s.folderid,s.foldername,s.parentfolder from cte c,SnehalKaQuestion s

where c.parentfolder = s.folderid)

select 'Level' + cast (ROW_NUMBER() over (order by folderid) as varchar) as Level ,foldername  from cte

end  

This stored Procedure is taking one argument as folder_name for which we need to find the hierarchy.

Let’s execute the procedure to find the result

Test 1 : With ‘a1’, to see the folder hierarchy of a1

exec proc_FindFolderLevel 'a1'

Level                               foldername

----------------------------------- ----------

Level1                              mail

Level2                              A

Level3                              a1

(3 row(s) affected)

Test 2 : With ‘b1’, to see the folder hierarchy of b1

exec proc_FindFolderLevel 'b1'

Level                               foldername

----------------------------------- ----------

Level1                              mail

Level2                              B

Level3                              b1

(3 row(s) affected)

Test 3 : With ‘A’, to see the folder hierarchy of A

exec proc_FindFolderLevel 'A'

Level                               foldername

----------------------------------- ----------

Level1                              mail

Level2                              A

(2 row(s) affected)

Test 4 : With ‘B’, to see the folder hierarchy of B

exec proc_FindFolderLevel 'B'

Level                               foldername

----------------------------------- ----------

Level1                              mail

Level2                              B

(2 row(s) affected)

Test 5: With ‘mail’, to see the folder hierarchy of mail

exec proc_FindFolderLevel 'mail'

Level                               foldername

----------------------------------- ----------

Level1                              mail

(1 row(s) affected)

Cool to witness, we can pull off this with CTE.

Heart beat is just an evidence of programmer, if your code is burning well; heart beat is just an ash...

Post Reference: Vikram Aristocratic Elfin Share