SQL SERVER – Simple Example of Recursive CTE

Recursive is the process in which the query executes itself. It is used to get results based on the output of base query.

Query:

if exists(select * from tempdb.sys.objects where name like '%#Emp_Dtls%')
drop table #Emp_Dtls

 select Distinct 1 As Emp_ID,'SureshBabu' As EmpName
 into #Emp_Dtls

 insert into #Emp_Dtls
 select Distinct 2 As Emp_ID,'Ravi' As EmpName

select * from #Emp_Dtls

Resultset:




 ;with Result
 (
 Emp_ID,
 Emp_Name,
 Emp_Name_New
 )
 as
 (
 select Emp_ID,
        LEFT(EmpName ,1),
        RIGHT(EmpName ,len(EmpName)-1)
 from #Emp_Dtls
 union all
 select Emp_ID,
        Left(Emp_Name_New,1),
        RIGHT(Emp_Name_New,Len(Emp_Name_New)-1)
 from Result
 where Emp_Name_New<>' '
 )
 select * from result
 order by 1 asc,len(Emp_Name_New) desc

Output: