title: How to write a SQL statement to return all children of each root,extend to each root
body: <p>I have a table.
  the value of [contents] can insert any word(Below are just examples)</p>
  
  <pre><code>create table #TreeTable (
       Id uniqueidentifier,       
       Contents NVARCHAR(200),
       ParentId uniqueidentifier,
       Floors Int,
       Sort Int,
  )
  
    insert into #TreeTable([Id], [ParentId], [Contents],[Floors],[Sort]) values
      (1, 0, '1',1,1),
      (2, 0, '2',1,2),
      (3, 1, '1-1',2,1),
      (4, 1, '1-2',2,2),
      (5, 2, '2-1',2,1),
      (6, 3, '1-1-1',3,1),
      (7, 4, '1-2-1',3,1);
      (8, 4, '1-2-2',3,2),
      (9, 6, '1-1-1-1',4,1),
      (10, 6,'1-1-1-2',4,2);
  </code></pre>
  
  <p>I want to write a T-SQL statement that will return all children of each root with the root ID　and Below is the result I expected</p>
  
  <pre><code>[Id][ParentId][Contents] [Levels][Sort]
  (1,   0,       '1',       1,      1)
  (3,   1,       '1-1',     2,      1)
  (6,   3,       '1-1-1',   3,      1)
  (9,   6,       '1-1-1-1', 4,      1)
  (10,  6,       '1-1-1-2', 4,      2)
  (4,   1,       '1-2',     2,      2)
  (7,   4,       '1-2-1',   3,      1)
  (8,   4,       '1-2-2',   3,      2)
  (2,   0,       '2',       1,      2)
  (5,   2,       '2-1',     2,      1)
  </code></pre>
  
  <p>The depth of the tree may be arbitrarily long</p>
  
  <p>I try to use this ，but the reuslt is not what i expected</p>
  
  <pre><code>with RecursiveTable as(
          ------ start-------
        SELECT a.*
        FROM TreeTable a
       WHERE
        a.ParentId  = 0
         ------ end ---------
          union all
        SELECT b.*
        FROM TreeTable b
        join RecursiveTable a on a.Id =b.ParentId
          )
       select * from RecursiveTable 
  </code></pre>