Wednesday, December 30, 2015

TSQL: count specific children in hierarchy

We have a project with hierarchical data: there are products nested in different categories and categories in its turn nested in segments. The goal was to count products and display this count on parent nodes. So basically we needed something like this:

As you can see there is amount of products displayed next to every node. Lets take a look on how to count those products using TSQL and CTE tables.

For brevity lets say we have data table that look like this:

Products have TypeID=4 and we want to count them.

Basically we need to count how much end nodes of certain type each node has. One of the ways it can be done is using CTE table:

 ;WITH ProductCte AS 
 (
  SELECT  
   RootID = ID,
   ID,
   TypeID
  FROM    
   Entity
  UNION ALL
  SELECT  
   cte.RootID,
   e.ID, 
   e.TypeID
  FROM   
   ProductCte cte
  INNER JOIN 
   Entity e ON e.ParentID = cte.ID
 )

 SELECT 
  e.ID,
  e.ParentID,
  e.TypeID,
  e.Name, 
  c.ProductCount AS Count
 FROM   
  Entity e
 INNER JOIN 
 (
  SELECT  
   ID = RootID, 
   ProductCount = COUNT(case TypeID when 4 then 1 else null end)
  FROM    
   ProductCte
  GROUP BY RootID
 ) c ON c.ID = e.ID
 WHERE TypeID != 4
 ORDER BY e.ID

Lets break this query down to two parts. First part is CTE table itself: the trick here is that we pinpoint ID of entity in anchor part of cte-table and duplicate it in RootID. This guarantees that recursive part of cte-table build us a result of all possible children for this RootID.

Second part of the query simply uses that big cte table result in order to count children of the specific type for every item in Entity table.

In the end we get the result like this:

Which is the list of hierarchy nodes from Entity table with products count for each node.

No comments :

Post a Comment