SQL Hierarchies – Parent Child

Posted on January 14, 2011

3



There are traditionally two ways of dealing with hierarchies in reporting – parent-child (Adjacency Lists) or levels. Uniform levels (such as Product Category -> Product Subcategory -> Product) are obviously easy to deal with via SQL. However these sorts of hierarchies are generally rare compared to ragged hierarchies that are most often expressed as parent-child relationships e.g:

So how do we deal with ragged hierarchies? There are a number of ways to do this and over the next few blogs I’m going to illustrate the options.  I started this project a couple of years ago, and then Itzik Ben-Gan came out with his excellent books on T-SQL and SQL Querying which would have saved me a lot of hard slogging.  I can’t recommend Itzik’s books highly enough – to me they are the best books available on T-SQL. For more information on hierarchies than I could post here, check out “Inside SQL Server 2008 – T-SQL Querying” by Itzik Ben Gan

Let’s start with the recursive Common Table Expression (CTE). There are many blogs out there on this topic already, but for the sake of completeness  I’ll include it here.

Here’s a basic example of using a CTE:

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

To get Baby Food and its’ descendants:

;WITH Descendants AS (
 SELECT  p.ProductName
                , p.ProductID
                , 0 AS HLevel
 FROM dbo.Product p  
 WHERE ProductName = 'Baby Food'
 UNION ALL
 SELECT p.ProductName
                , p.ProductID
                , H.HLevel+1
 FROM dbo.Product p  
 INNER JOIN Descendants H
 ON H.ProductID=p.ParentProductID  
)  
SELECT ProductID, REPLICATE('     ', Hlevel) + ProductName AS ProductName
FROM Descendants d

This will produce:

To get the ancestors of Baby Cereal:

;WITH Ancestors
AS 
( 
 SELECT  p.ProductName
  , p.ProductID
  , ParentProductID
  , 1 AS HLevel
 FROM dbo.Product p  
 WHERE ProductName = 'Baby Cereal'
 UNION ALL
 SELECT p.ProductName
  , p.ProductID
  , p.ParentProductID
  , H.HLevel+1
 FROM dbo.Product p  
 INNER JOIN Ancestors H
 ON H.ParentProductID=p.ProductID  
)  
SELECT ProductName, ProductID, HLevel
FROM Ancestors c 
GO

This produces:


Advertisements
Posted in: SQL