title: Write a query to get hierarchical data in SQL
body: <p>I am using MS SQL 2008 R2, i want to retrive the data according to employee 
  hierarchy order by oldest to newest data as given in required output section</p>
  
  <p>My Table Data is As Following : </p>
  
  <pre><code>SELECT * FROM 
  (
      SELECT Code = 1  ,ParentCode = NULL, EmployeeName  = 'Hemant'
      UNION ALL
      SELECT Code = 2  ,ParentCode = 1, EmployeeName  = 'Sachin'
      UNION ALL
      SELECT Code = 3  ,ParentCode = 2, EmployeeName  = 'Shanti'
      UNION ALL
      SELECT Code = 4  ,ParentCode = 1, EmployeeName  = 'Sandesh'
      UNION ALL
      SELECT Code =  5  ,ParentCode = 3, EmployeeName  = 'Sandeep'
      UNION ALL
      SELECT Code = 6  ,ParentCode = NULL, EmployeeName  = 'Smily'
      UNION ALL
      SELECT Code = 7  ,ParentCode = 6, EmployeeName  = 'Snehi'
      UNION ALL
      SELECT Code =  8  ,ParentCode = 6, EmployeeName  = 'Kiran'
      UNION ALL
      SELECT Code =  9  ,ParentCode = 7, EmployeeName  = 'Shital'
      UNION ALL
      SELECT Code =  10  ,ParentCode = 9, EmployeeName  = 'Simran'
  ) X
  </code></pre>
  
  <p>Required OutPut :</p>
  
  <pre><code>Code    Name        ParentCode   Parent
  1      Hemant       NULL         NULL
  2      Sachin       1            Hemant
  3      Shanti       2            Sachin
  5      Sandeep      3            shanti
  4      Sandesh      1            Hemant
  6      Smily        NULL         NULL
  7      Snehi        6            Smily
  9      Shital       7            Snehi
  10     Simran       9            Shital
  8      Kiran        6            Smily
  </code></pre>