Recursive Queries using Common Table Expressions (CTE)

       Sometimes we have data in a Hierarchical format, and we are to perform some Insert/Update/Delete operation over there. In this situation if the DataBase Server is SQL Server 2000 or lower version, then the task becomes bit complex and need to apply the logic with the help of views, cursors, temp tables or any other object.

        But, if the Database Server is SQL Server 2005 or later version, then the task becomes easier and can implement the Common Table Expressions (CTE).

        Here is an example to elaborate the concept. Let's say we have table UserDetails as below :

UserID
CityName
ParentID
U001
Mumbai
U001
U002
Bhopal
U002
U003
Delhi
U003
U004
Bhopal
U002
U005
Mumbai
U001
U006
Delhi
U003
U007
Mumbai
U001
U008
Delhi
U003
U009
Bhopal
U002
U010
Mumbai
U001
U011
Delhi
U003
U012
Mumbai
U001

    Now from the above table we like to get all the users which come under the hierarchy of user having UserID as "U001".

  To get the above result, we will apply the CTE with below code :

;WITH CTE

AS

(

      SELECT * FROM UserDetails  

      WHERE UserID = 'U001' 



      UNION ALL



        


      SELECT  MS.*

      FROM    UserDetails MS



              ON MP.UserID = MS.ParentID 
              INNER JOIN CTE MP 

)

SELECT * FROM CTE
ORDER BY UserID

 Executing the above code, we will get the below ouput :

UserID
CityName
ParentID
U001
Mumbai
U001
U005
Mumbai
U001
U007
Mumbai
U005
U010
Mumbai
U007
U012
Mumbai
U005

 Reference: Govind Badkur(http://sqlserver20.blogspot.com)