title: recursive cte - mark all leafs
body: <p>I have a recursive CTE that's working fine, but I need one more thing: add [isLeaf] flag to each result, which will tell if the record has any more children ([Leafs] field with children counter would be even better).</p>
  
  <p>Working example pasted below. It counts the level of every category and joins names into category path, but sql server doesn't allow LEFT JOIN, TOP, SELECT DISTINCT, aggregates and subqueries to be used in recursive part of CTE, which are the obvious methods of doing what I need.</p>
  
  <pre><code>DROP TABLE cats
  GO
  create table cats(
      catid int primary key clustered,
      parent int, --parent's catid. 0 for top-level entries
      name    varchar(255)
  )
  GO
  
  insert into cats (catid, parent, name)
  select 1 as catid, 0 as parent, 'computers' as name union all 
      select 2, 1, 'laptops' union all 
          select 4, 2, 'ibm' union all 
          select 5, 2, 'others' union all 
      select 3, 1, 'desktops' union all 
          select 6, 3, 'amd' union all 
          select 7, 3, 'others' union all 
  select  8, 0 , 'cars' union all 
      select 9, 8, 'others' union all 
      select 10, 8, 'pickups' union all 
          select 11, 10, 'others' union all 
          select 12, 10, 'ford' union all 
              select 14, 12, 'ranger' union all 
              select 15, 12, 'others'
  GO      
  
  ;with cteCat as (
      select 
          CatId, Parent,
          [cteLevel]  = 1,
          [ctePath]   = cast(Name as varchar(4000))
          --,[cteIsLeaf]  = 0
      from cats
      where 1=1
          and Parent=0
  union all 
      select 
          c.CatId, c.Parent,
          [cteLevel] = cc.cteLevel+1,
          [ctePath] = cast(cc.ctePath + ' | ' + c.Name as varchar(4000))
          --,[cteIsLeaf]  = 0 --???--
      from cats c
      join cteCat cc
          on c.Parent = cc.CatId
      where 1=1
          and c.Parent&lt;&gt;0
  )
  select 
      * 
  from cteCat
  order by 
      catid
  </code></pre>