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

Thanks for the sample data but also include a sample expected output — Mazhar
– Mazhar, Commented May 2, 2018 at 14:05
Do you just need counts at the given level? Presuming the data in column depth is accurate, all you really need to do is a group by on depth. — Philip Kelley
– Philip Kelley, Commented May 2, 2018 at 14:06
...this is more complex than that, isn't it? Is string parsing involved?Agree with @Mazhar, need to see a sample of your desired results. — Philip Kelley
– Philip Kelley, Commented May 2, 2018 at 14:16
Your output doesn't make any sense at all. Why only those two rows? And where do those values comes from? They are not in the sample data. The biggest issue I see here is that you are storing delimited data. This violates 1NF and causes incredible amounts of pain. — Sean Lange
– Sean Lange, Commented May 2, 2018 at 14:30
(Posts like this, I always assume the poster cannot modify the data structure. If thems what originally designed databases would get it right, there'd be far, far fewer database-related posts on SO.) — Philip Kelley
– Philip Kelley, Commented May 2, 2018 at 14:38

Vladislav · Accepted Answer · 2018-05-03 07:47:36Z

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

Thank you! I edited to my requirement and your solution works perfectly!

LukStorms · Accepted Answer · 2018-05-03 11:11:30Z

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;

Collectives™ on Stack Overflow

CTE Recursion using group by at a specific depth

2 Answers 2

1 Comment

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

Comments

Your Answer

Sign up or log in

Post as a guest

Related