SQL Server CTE and recursion example

Question

I never use CTE with recursion  I was just reading an article on it  This article shows employee info with the help of Sql server CTE and recursion  It is basically showing employees and their manager info  I am not able to understand how this query works  Here is the query   WITH   cteReports  EmpID  FirstName  LastName  MgrID  EmpLevel    AS         SELECT EmployeeID  FirstName  LastName  ManagerID  1     FROM Employees     WHERE ManagerID IS NULL     UNION ALL     SELECT e EmployeeID  e FirstName  e LastName  e ManagerID        r EmpLevel   1     FROM Employees e       INNER JOIN cteReports r         ON e ManagerID   r EmpID     SELECT   FirstName         LastName AS FullName    EmpLevel     SELECT FirstName         LastName FROM Employees     WHERE EmployeeID   cteReports MgrID  AS Manager FROM cteReports ORDER BY EmpLevel  MgrID   Here I am posting about how the output is showing    I just need to know how it is showing manager first and then his subordinate in a loop  I guess the first sql statement fires only once and that returns all employee ids   And the second query repeatedly fires  querying the database on which employee exists with the current manager id   Please explain how the sql statement executes in an internal loop and also tell me the sql execution order  Thanks   MY 2nd phase of question   WITH Numbers AS       SELECT n   1     UNION ALL     SELECT n   1     FROM Numbers     WHERE n 1  lt   10   SELECT n FROM Numbers   Q 1  how is the value of N is getting incremented  if the value is assigned to N every time then N value can be incremented but only the first time N value was initialized   Q 2  CTE and recursion of employee relations   The moment I add two managers and add a few more employees under the second manager is where the problem starts   I want to display the first manager detail and in the next rows only those employee details that relate to the subordinate of that manager   Suppose  ID     Name      MgrID    Level ---    ----      ------   ----- 1      Keith      NULL     1 2      Josh       1        2 3      Robin      1        2 4      Raja       2        3 5      Tridip     NULL     1 6      Arijit     5        2 7      Amit       5        2 8      Dev        6        3   I want to display the results in such way with CTE expressions  Please tell me what to modify in my sql which I gave here in order to pull manager-employee relations  Thanks   I want the output to be like this   ID          Name   MgrID       nLevel      Family ----------- ------ ----------- ----------- -------------------- 1           Keith  NULL        1           1 3           Robin  1           2           1 2           Josh   1           2           1 4           Raja   2           3           1 5           Tridip NULL        1           2 7           Amit   5           2           2 6           Arijit 5           2           2 8           Dev    6           3           2   Is this possible

User · Answer

The execution process is really confusing with recursive CTE, I found the best answer at https://technet.microsoft.com/en-us/library/ms186243(v=sql.105).aspx and the abstract of the CTE execution process is as below.

The semantics of the recursive execution is as follows:

Split the CTE expression into anchor and recursive members.
Run the anchor member(s) creating the first invocation or base result set (T0).
Run the recursive member(s) with Ti as an input and Ti+1 as an output.
Repeat step 3 until an empty set is returned.
Return the result set. This is a UNION ALL of T0 to Tn.

User · Answer

--DROP TABLE  Employee     CREATE TABLE  Employee EmpId BIGINT IDENTITY EmpName VARCHAR 25  Designation VARCHAR 25  ManagerID BIGINT       INSERT INTO  Employee VALUES  M11M   Manager  NULL      INSERT INTO  Employee VALUES  P11P   Manager  NULL       INSERT INTO  Employee VALUES  AA   Clerk  1      INSERT INTO  Employee VALUES  AB   Assistant  1      INSERT INTO  Employee VALUES  ZC   Supervisor  2      INSERT INTO  Employee VALUES  ZD   Security  2        SELECT   FROM  Employee  NOLOCK             WITH Emp CTE      AS               SELECT EmpId EmpName Designation  ManagerID                CASE WHEN ManagerID IS NULL THEN EmpId ELSE ManagerID END ManagerID N         FROM  Employee             select EmpId EmpName Designation  ManagerID     FROM Emp CTE     order BY ManagerID N  EmpId

User · Answer

I haven t tested your code  just tried to help you understand how it operates in comment   WITH   cteReports  EmpID  FirstName  LastName  MgrID  EmpLevel    AS     -- gt  gt  gt  gt  gt  gt  gt  gt  gt  gt Block 1 gt  gt  gt  gt  gt  gt  gt  gt  gt  gt  gt  gt  gt  gt  gt  gt  gt  -- In a rCTE  this block is called an  Anchor  -- The query finds all root nodes as described by WHERE ManagerID IS NULL     SELECT EmployeeID  FirstName  LastName  ManagerID  1     FROM Employees     WHERE ManagerID IS NULL -- gt  gt  gt  gt  gt  gt  gt  gt  gt  gt Block 1 gt  gt  gt  gt  gt  gt  gt  gt  gt  gt  gt  gt  gt  gt  gt  gt  gt      UNION ALL -- gt  gt  gt  gt  gt  gt  gt  gt  gt  gt Block 2 gt  gt  gt  gt  gt  gt  gt  gt  gt  gt  gt  gt  gt  gt  gt  gt  gt      -- This is the recursive expression of the rCTE -- On the first  execution  it will query data in  Employees   -- relative to the  Anchor  above  -- This will produce a resultset  we will call it R 1  and it is JOINed to  Employees  -- as defined by the hierarchy -- Subsequent  executions  of this block will reference R n-1      SELECT e EmployeeID  e FirstName  e LastName  e ManagerID        r EmpLevel   1     FROM Employees e       INNER JOIN cteReports r         ON e ManagerID   r EmpID -- gt  gt  gt  gt  gt  gt  gt  gt  gt  gt Block 2 gt  gt  gt  gt  gt  gt  gt  gt  gt  gt  gt  gt  gt  gt  gt  gt  gt      SELECT   FirstName         LastName AS FullName    EmpLevel     SELECT FirstName         LastName FROM Employees     WHERE EmployeeID   cteReports MgrID  AS Manager FROM cteReports ORDER BY EmpLevel  MgrID   The simplest example of a recursive CTE I can think of to illustrate its operation is    WITH Numbers AS       SELECT n   1     UNION ALL     SELECT n   1     FROM Numbers     WHERE n 1  lt   10   SELECT n FROM Numbers   Q 1  how value of N is getting incremented  if value is assign to N every time then N value can be incremented but only first time N value was initialize   A1  In this case  N is not a variable  N is an alias  It is the equivalent of SELECT 1 AS N  It is a syntax of personal preference  There are 2 main methods of aliasing columns in a CTE in T-SQL  I ve included the analog of a simple CTE in Excel to try and illustrate in a more familiar way what is happening   --  Outside  WITH CTE  MyColName  AS       SELECT 1   -- Inside  WITH CTE AS       SELECT 1 AS MyColName     -- Or     SELECT MyColName   1       -- Etc          Q 2  now here about CTE and recursion of employee relation the moment i add two manager and add few more employee under second manager then problem start  i want to display first manager detail and in the next rows only those employee details will come those who are subordinate of that manager  A2   Does this code answer your question   -------------------------------------------- -- Synthesise table with non-recursive CTE --------------------------------------------  WITH Employee  ID  Name  MgrID  AS        SELECT 1        Keith        NULL   UNION ALL     SELECT 2        Josh         1      UNION ALL     SELECT 3        Robin        1      UNION ALL     SELECT 4        Raja         2      UNION ALL     SELECT 5        Tridip       NULL   UNION ALL     SELECT 6        Arijit       5      UNION ALL     SELECT 7        Amit         5      UNION ALL     SELECT 8        Dev          6      -------------------------------------------- -- Recursive CTE - Chained to the above CTE --------------------------------------------  Hierarchy AS       --  Anchor     SELECT   ID              Name              MgrID              nLevel   1              Family   ROW NUMBER   OVER  ORDER BY Name      FROM Employee     WHERE MgrID IS NULL      UNION ALL     --  Recursive query     SELECT   E ID              E Name              E MgrID              H nLevel 1              Family     FROM Employee   E     JOIN Hierarchy  H ON E MgrID   H ID   SELECT   FROM Hierarchy ORDER BY Family  nLevel   Another one sql with tree structure  SELECT ID space nLevel                       CASE WHEN nLevel  gt  1 THEN nLevel ELSE 0 END                    Name FROM Hierarchy ORDER BY Family  nLevel

User · Answer

Would like to outline a brief semantic parallel to an already correct answer   In  simple  terms  a recursive CTE can be semantically defined as the following parts   1  The CTE query  Also known as ANCHOR   2  The recursive CTE query on the CTE in  1  with UNION ALL  or UNION or EXCEPT or INTERSECT  so the ultimate result is accordingly returned   3  The corner termination condition  Which is by default when there are no more rows tuples returned by the recursive query   A short example that will make the picture clear    WITH SupplierChain CTE supplier id  supplier name  supplies to  level  AS   SELECT S supplier id  S supplier name  S supplies to  0 as level FROM Supplier S WHERE supplies to   -1    -- Return the roots where a supplier supplies to no other supplier directly  UNION ALL  -- The recursive CTE query on the SupplierChain CTE SELECT S supplier id  S supplier name  S supplies to  level   1 FROM Supplier S INNER JOIN SupplierChain CTE SC ON S supplies to   SC supplier id   -- Use the CTE to get all suppliers in a supply chain with levels SELECT   FROM SupplierChain CTE   Explanation  The first CTE query returns the base suppliers  like leaves  who do not supply to any other supplier directly  -1   The recursive query in the first iteration gets all the suppliers who supply to the suppliers returned by the ANCHOR  This process continues till the condition returns tuples   UNION ALL returns all the tuples over the total recursive calls   Another good example can be found here   PS  For a recursive CTE to work  the relations must have a hierarchical  recursive  condition to work on  Ex  elementId   elementParentId   you get the point

[sql-server] SQL Server CTE and recursion example

Examples related to sql-server

Examples related to common-table-expression