CODEWITHSUNDEEP
sqlt-sqlrecursionsql-server-2008-r2common-table-expression
AdamL
AdamL

Reputation: 13161

recursive cte - mark all leafs

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).

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.

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<>0
)
select 
    * 
from cteCat
order by 
    catid

Upvotes: 4

Views: 1647

Answers (1)

Nenad Zivkovic
Nenad Zivkovic

Reputation: 18559

Easiest thing to implement is just to add corraleted subquery in the final select statement that checks for IsLeaf. It's a simple check to see if particular CatID is someone's parent or not. Does not require recursion. 

SELECT
    * ,  CASE WHEN EXISTS (SELECT * FROM cats c2 WHERE c2.parent = c1.CatID) THEN 0 ELSE 1 END AS IsLeaf
FROM cteCat c1
ORDER BY
    catid

EDIT: If you need [Leafs] as count of just the imminent children it's also simple to get them:

SELECT
    * 
    , CASE WHEN EXISTS (SELECT * FROM cats c2 WHERE c2.parent = c1.CatID) THEN 0 ELSE 1 END AS IsLeaf
    , (SELECT COUNT(*) FROM cats c2 WHERE c2.parent = c1.CatID) AS Leafs
FROM cteCat c1
ORDER BY 
    c1.catid

SQLFiddle DEMO

But, If you need [Leafs] as total counter of children and all of chieldren's children it would require rewriting your CTE (to go bottom-up instead of top-to-bottom).

Upvotes: 4

Related Questions