CTE Recursion using group by at a specific depth

Question

I have a table representing a folder structure - (i.e recursion on id and parent field) - this data actually represents items within a folder structure (the item field is simply there for the example, however my case will be a join to many items. I have created a path string (does not need to be used) - this string represents the path of each record in the data-set(branch field)

my query needs to filter by all records that are more than 3 levels deep, then count any items that are at 3 levels or lower in each recursion branch ( in my data, this would mean records 5 & 6 represent top level nodes and will be records used for the grouping.

Any assistance?

DECLARE @tbl TABLE
  ( 
   Id int
  ,ParentId int
  ,branch varchar(100)
  ,depth int
  ,item varchar(20)
  )
INSERT  INTO @tbl
        ( Id, ParentId,item )
VALUES 
(1, NULL,Null),
(2, 1,Null),
(3, 1,Null),
(4, 3,Null),
(5, 4,Null),
(6, 5,'car'),
(7, 6,'bus'),
(8, 7,'truck'),
(9, 8,'car'),
(10,8,'bike'),
(11,5,'car'),
(12,5,'truck'),
(13,4,'truck'),
(14,8,'bike'),
(15,8,'bus');

--select t_package.package_id, t_package.parent_ID from t_package 

;WITH abcd
        AS (
              -- anchor
            SELECT   id
                    ,ParentID
                    ,CAST(id AS VARCHAR(100)) AS [Path]
                    ,0 as depth
                    ,item
            FROM    @tbl
            WHERE   ParentId is Null
            UNION ALL
              --recursive member
            SELECT  t.id
                   ,t.ParentID
                   ,CAST(a.[Path] + ',' + CAST( t.ID AS VARCHAR(100)) AS varchar(100)) AS [Path]
                   ,a.depth +1
                   ,t.item
            FROM    @tbl AS t
                    JOIN abcd AS a ON t.ParentId = a.id
           )
insert into @tbl (id,parentID,branch,depth,item) select * from abcd

select * from @tbl 
where branch is not null

This would mean that if you grouped at level 3, and required the counts of items at each level, your result set would look something like this:

ID     Depth  -- car -- bike -- bus -- truck
5      3      --  3  --  2   -- 2   --  2  
13     3      --  0  --  0   -- 0   --  1

Answer 1

You could also initiate the recursive CTE at level 3.
Then pivot for the items.

Example code:

DECLARE @tbl TABLE(ID INT, ParentID INT, Item VARCHAR(20));
INSERT INTO @tbl (ID, ParentID, Item) VALUES
                            (1,  NULL, NULL),
                        (2,  1, NULL),
                        (3,  1, NULL),
                    (4,  3, NULL),
                (5,  4, NULL),
            (6,  5, 'car'),
        (7,  6, 'bus'),
    (8,  7, 'truck'),
(9,  8, 'car'),
(10, 8, 'bike'),
            (11, 5, 'car'),
            (12, 5, 'truck'),
                 (13, 4, 'truck'),
(14, 8, 'bike'),
(15, 8, 'bus');

;with CTE as (
    select t0.ID as tier0id, t1.ID as tier1id, t2.ID as tier2id, t3.ID as tier3id, 0 as lvl, t3.ID, t3.ParentID, t3.Item
    from @tbl t0
    join @tbl t1 on (t0.ParentID is null and t1.ParentID = t0.ID)
    join @tbl t2 on (t2.ParentID = t1.ID)
    join @tbl t3 on (t3.ParentID = t2.ID)

    union all

    select tier0id, tier1id, tier2id, tier3id, lvl + 1, t.ID, t.ParentID, t.Item
    from CTE
    join @tbl t on (t.ParentID = CTE.ID)
)
select *
from (
    select distinct tier3id as ID, 3 as Depth, ID as ChildId, Item 
    from CTE
) q
pivot (
    count(ChildId) 
    for Item in ([car], [bike], [bus], [truck])
) pvt
order by ID;

Answer 2

This was quite intriguing so I had to push my previous experience with CTEs.

Following is a query that should complete your goal. Mind, however, that in the way you have given the desired result, you will always end-up hard-coding the Item values within your query. Most probably if the result domain of values for Item is changing, you will have to consider writing a dynamic SQL. In below solution I'm using "conditional" aggregation, which has been proven to not be the "performance-breaker". I'm sure another approach could be introduced with a PIVOT syntax. Performance, should be further analyzed and maybe some middle-indexing-scenario introduced.

One way or another this should do it and hopefully it brings you some benefit:

DECLARE @summaryDepth INT = 3;

DECLARE @tbl TABLE( 
    ID INT, ParentID INT, Item VARCHAR(20));
INSERT @tbl (
    ID, ParentID, Item)
VALUES
    (1,  NULL, NULL),
    (2,  1, NULL),
    (3,  1, NULL),
    (4,  3, NULL),
    (5,  4, NULL),
    (6,  5, 'car'),
    (7,  6, 'bus'),
    (8,  7, 'truck'),
    (9,  8, 'car'),
    (10, 8, 'bike'),
    (11, 5, 'car'),
    (12, 5, 'truck'),
    (13, 4, 'truck'),
    (14, 8, 'bike'),
    (15, 8, 'bus');

/*
(1, NULL, NULL),*/
--(2, 1, NULL),
--(3, 1, NULL),
----(4, 3, NULL),
------(13,4,'truck'),
------(5, 4, NULL),
--------(6, 5,'car'),
--------(11,5,'car'),
--------(12,5,'truck'),
----------(7, 6,'bus'),
------------(8, 7,'truck'),
--------------(9, 8,'car'),
--------------(10,8,'bike'),
--------------(14,8,'bike'),
--------------(15,8,'bus');

;WITH CTE_Hierarchy AS (
    SELECT 
        ID, ParentID, Item,
        CAST(ID AS VARCHAR(100)) AS [Path],
        0 [Depth],
        CASE 
            WHEN @summaryDepth = 0 THEN ID 
            ELSE NULL 
        END [SummaryDepthRootID]
    FROM @tbl
    WHERE ParentId IS NULL

    UNION ALL

    SELECT
        child.ID, child.ParentID, child.Item,
        CAST(parent.[Path] + '/' + CAST(child.ID AS VARCHAR(100)) AS VARCHAR(100)) [Path],
        parent.[Depth] + 1 [Depth],
        CASE 
            WHEN parent.SummaryDepthRootID IS NOT NULL THEN parent.SummaryDepthRootID
            WHEN @summaryDepth = (parent.[Depth] + 1) THEN child.ID 
            ELSE NULL 
        END [SummaryDepthRootID]
    FROM @tbl AS child
    JOIN CTE_Hierarchy AS parent ON parent.ID = child.ParentID
)
SELECT 
    SummaryDepthRootID [ID],
    @summaryDepth [Depth],
    COUNT (CASE WHEN Item='car' THEN 1 ELSE NULL END) [car],
    COUNT (CASE WHEN Item='bike' THEN 1 ELSE NULL END) [bike],
    COUNT (CASE WHEN Item='bus' THEN 1 ELSE NULL END) [bus],
    COUNT (CASE WHEN Item='truck' THEN 1 ELSE NULL END) [truck]
FROM CTE_Hierarchy
WHERE SummaryDepthRootID IS NOT NULL
GROUP BY SummaryDepthRootID;
GO