title: Applying recursion to a SQL Server query
body: <p>The table in SQL Server has mapping of parent to child references of many generations.</p>
  
  <p>I have to achieve result:</p>
  
  <pre><code>Level No || Child Count
  </code></pre>
  
  <p>So, I have following query for it : </p>
  
  <pre><code>SELECT 
      'L1' LEVEL, COUNT(ChildRef) CHILD 
  FROM 
      [dbo].[TOMatriX] 
  WHERE 
      ParentRef = 1
  
  UNION ALL
  
  SELECT 
     'L2' LEVEL, COUNT(ChildRef) CHILD 
  FROM 
     [dbo].[TOMatriX]
  WHERE 
     ParentRef IN (SELECT ChildRef FROM [dbo].[TOMatriX] WHERE ParentRef = 1)
  
  UNION ALL
  
  SELECT 
     'L3' LEVEL, COUNT(ChildRef) CHILD 
  FROM 
     [dbo].[TOMatriX]
  WHERE 
     ParentRef IN (SELECT ChildRef FROM [dbo].[TOMatriX]
                   WHERE ParentRef IN (SELECT ChildRef FROM [dbo].[TOMatriX] 
                                       WHERE ParentRef = 1)
                  )
  
  UNION ALL
  
  SELECT 
     'L4' LEVEL, COUNT(ChildRef) CHILD 
  FROM 
     [dbo].[TOMatriX]
  WHERE 
     ParentRef IN (SELECT ChildRef FROM [dbo].[TOMatriX]
                   WHERE ParentRef IN (SELECT ChildRef FROM [dbo].[TOMatriX]
                                       WHERE ParentRef IN (SELECT ChildRef 
                                                           FROM [dbo].[TOMatriX] 
                                                           WHERE ParentRef = 1)
                                      )
                  )
  </code></pre>
  
  <p>How can make this query dynamic &amp; not hardcoded like this because levels can go above 4 also.</p>
  
  <p>How to use <a href="http://goo.gl/SoeN1k" rel="nofollow">Common Table Expressions</a> or any recursion in this?</p>