title: SQL Server: Hierarchical query with depth
body: <p>I have to show employee and his manager name with depth.</p>
  
  <p>If Simon is top manager and Katie's manager is Simon and John's manager is Katie, then Simon's level would be 0 because he is top and Katie's level will be 1 and John's level will be 2. </p>
  
  <p>I tried below way but not getting desired output. Please tell me what to rectify in code.</p>
  
  <p>My code:</p>
  
  <pre><code>Declare @ID INT
  SET @ID = 6;
  
  WITH EmployeeCTE AS
  (
      SELECT
          ID, Name, MgrID, 0 AS level 
      FROM 
          Employee
      WHERE 
          ID = @ID
  
      UNION ALL
  
      SELECT 
          emp.ID, emp.Name, emp.MgrID, level+1 AS level 
      FROM 
          Employee emp
      JOIN 
          EmployeeCTE ON emp.ID = EmployeeCTE.MgrID
  )
  --select * from EmployeeCTE
  SELECT 
      e1.Name, ISNULL(e2.Name, 'Top BOSS') as [Manager Name], 
      e2.level 
  FROM
      EmployeeCTE e1 
  LEFT JOIN 
      EmployeeCTE e2 ON e1.MgrID = e2.ID 
  </code></pre>