EnumeratedPath

Posted on January 14, 2011

4



Continuing on our journey of variations of SQL Hierarchies we come to the Enumerated Path. These are especially useful to know, even if you don’t plan to use it for a hierarchy.

By using a Recursive CTE we can enumerate the path from the root to the leaf e.g.

This:

Becomes:

Let’s look at the SQL to do this:

IF OBJECT_ID('dbo.Product') IS NOT NULL DROP TABLE dbo.Product
GO
CREATE TABLE dbo.Product(ProductID int not null
, ProductName varchar(100)
, ParentProductID INT)
GO
ALTER TABLE dbo.Product ADD CONSTRAINT
 PK_Product PRIMARY KEY CLUSTERED
 (
 ProductID
 ) ON [PRIMARY]
GO
INSERT INTO dbo.Product(ProductID, ProductName, ParentProductID)
VALUES (0,'Baby Goods',NULL)
, (10,'Baby Food',0)
, (20,'Nappies',0)
, (100,'All Ages Baby Food',10)
, (150,'Beginners',10)
, (200,'Strawberry Yoghurt',100)
, (250,'Baby Cereal',100)
, (300,'Formula Milk',150)
, (310,'Heinz Formula',300)
, (400,'Small Pack Nappies',20)
, (450,'Bulk Pack Nappies',20)
GO
IF OBJECT_ID('dbo.ProductEnumeratedPath') IS NOT NULL DROP TABLE ProductEnumeratedPath
GO
;WITH ProductLevels
AS
( 
 SELECT  p.ProductName
  , p.ProductID
  , 1 AS HLevel
  , '.' + CONVERT(VARCHAR(MAX), ProductID) AS TreePath
  , ParentProductID  
 FROM dbo.Product p  
 WHERE p.ParentProductID IS NULL
 UNION ALL
 SELECT p.ProductName
  , p.ProductID
  , x.HLevel+1
  , x.TreePath + '.' + CONVERT(VARCHAR(MAX), p.ProductID) AS TreePath
  , p.ParentProductID  
 FROM dbo.Product p  
 INNER JOIN ProductLevels x
 ON x.ProductID=p.ParentProductID  
)  
SELECT ProductName, ProductID, ParentProductID, Hlevel, TreePath + '.' AS TreePath
INTO ProductEnumeratedPath
FROM ProductLevels
GO 

Essentially we are just adding each descendant on to the end of the string in TreePath.

Let’s get the descendants of Baby Food. To do this we do a self-join. Note the join condition: child.TreePath LIKE parent.TreePath + ‘%’

DECLARE @Root VARCHAR(100);
SET @Root = 'Baby Food'
SELECT child.ProductID
, REPLICATE('     ', child.Hlevel) + child.ProductName AS ProductName
, child.TreePath
FROM ProductEnumeratedPath child
INNER JOIN  ProductEnumeratedPath parent
ON child.TreePath LIKE parent.TreePath + '%'
WHERE parent.ProductName = @Root
ORDER BY child.TreePath

Produces:

 

To get the ancestors of Heinz Formula:

DECLARE @Root VARCHAR(100);
SET @Root = 'Heinz Formula'
SELECT  parent.ProductID
 , REPLICATE('     ', parent.Hlevel) + parent.ProductName AS ProductName
 , parent.TreePath
FROM dbo.ProductEnumeratedPath child
INNER JOIN  dbo.ProductEnumeratedPath parent
ON child.TreePath LIKE parent.TreePath + '%'
WHERE child.ProductName = @Root

Produces:

So again, how do we join the results of these hierarchy queries to a sales table:

IF OBJECT_ID('dbo.ProductSale') IS NOT NULL DROP TABLE dbo.ProductSale
CREATE TABLE dbo.ProductSale(SaleDate DATE, ProductID INT, SaleQty INT)
GO
INSERT INTO dbo.ProductSale
VALUES (GETDATE(), 200, 50)
, (GETDATE(),250, 90)
, (GETDATE(), 310, 80)
, (GETDATE(), 400, 23)
, (GETDATE(), 450, 19)
;WITH CTE AS (
 SELECT a.ProductID, b.ParentProductID , b.ProductName
 FROM dbo.ProductEnumeratedPath a
 JOIN dbo.ProductEnumeratedPath b
 on a.TreePath LIKE b.TreePath + '%'
 WHERE NOT EXISTS (  SELECT 1
      FROM dbo.ProductEnumeratedPath c
      WHERE c.ParentProductID = a.ProductID)
    )
SELECT ProductName, SUM(ps.SaleQty) AS SaleQty
FROM dbo.ProductSale ps
JOIN CTE c
ON c.ProductID = ps.ProductID  
GROUP BY c.ParentProductID,ProductName

This produces:

 

ProductName

SaleQty

Baby Goods

262

Baby Food

220

Nappies

42

All Ages Baby Food

140

Beginners

80

Bulk Pack Nappies

19

Small Pack Nappies

23

Baby Cereal

90

Strawberry Yoghurt

50

Formula Milk

80

Heinz Formula

80


 

Advertisements
Posted in: SQL