title: SQL Server CTE and recursion example
body: <p>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:</p>
  
  <pre><code>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
  </code></pre>
  
  <p>Here I am posting about how the output is showing:
  <img src="https://i.stack.imgur.com/03JpM.jpg" alt="enter image description here"></p>
  
  <p>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.</p>
  
  <p>And the second query repeatedly fires, querying the database on which employee exists with the current manager id.</p>
  
  <p>Please explain how the sql statement executes in an internal loop and also tell me the sql execution order. Thanks.</p>
  
  <h2>MY 2nd phase of question</h2>
  
  <pre><code>;WITH Numbers AS
  (
      SELECT n = 1
      UNION ALL
      SELECT n + 1
      FROM Numbers
      WHERE n+1 &lt;= 10
  )
  SELECT n
  FROM Numbers
  </code></pre>
  
  <p>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.</p>
  
  <p>Q 2) CTE and recursion of employee relations:</p>
  
  <p>The moment I add two managers and add a few more employees under the second manager is where the problem starts.</p>
  
  <p>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.</p>
  
  <h2>Suppose</h2>
  
  <pre><code>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
  </code></pre>
  
  <p>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.</p>
  
  <h2>I want the output to be like this:</h2>
  
  <pre><code>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
  </code></pre>
  
  <p>Is this possible...?</p>