Categories
Data

Get Ancestor and Descendant IDs or info using SQL 2005 Common Table Expressions

Need some recursive processing in TSQL (SQL Server 2005)?

Needed some TSQL code again to retrieve descendant and ancestor entries in a self-referencing table. I've done this a couple of times already and although I can write on top of my mind, sometimes you just want to make life easier and have a script you can just modify a bit to fit the new requirement. [more]

So what I usually use for recursions in SQL Server 2005 are Common Table Expressions (the "WITH" keyword). This is only available in SQL 2005 and very easy/efficient for recursive.

To read more about it check this link from MSDN : Using Common Table Expressions

And of course the ever helpful Books Online (search for maybe WITH or Common Table Expressions) 

Nevertheless here's my basic examples to get "Descendants and Self" and "Ancestors and Self" information. In this case I only involved the ID, ParentID, and Sequence but you may add more columns (ID and ParentID are required to work). Also you might want to just retrieve the ID and just perform a JOIN afterwards.

   11   — NOTE: replace these accordingly

   12   — replace columns : MyEntityID, MyParentEntityID

   13   — replace parameter : @MyEntityIDParameter

   14   — replace table: MyTable

   15   — also note that the condition for anchor and recursive part

   16   — and the final consumption (select statement after the WITH block)

   17   — can be changed dependending on requirement

   18   WITH DescendantsAndSelf([ID], [ParentID], [Sequence]) AS

   19   (

   20       — self (anchor part)

   21       SELECT

   22         [ID] = MyEntityID,

   23         [ParentID] = MyParentEntityID,

   24         [Sequence] = 1

   25       FROM MyTable

   26       WHERE

   27         MyEntityID = @MyEntityIDParameter

   28 

   29       UNION ALL

   30 

   31       — descendants

   32       SELECT

   33         [ID] = MyEntityID,

   34         [ParentID] = MyParentEntityID,

   35         [Sequence] = [Sequence] + 1

   36       FROM DescendantsAndSelf AS CTE, MyTable AS T1

   37       WHERE CTE.[ID] = T1.MyParentEntityID

   38   )

   39 

   40     SELECT *

   41     FROM DescendantsAndSelf

Download above code as text file > CTE_descendantsAndSelf.txt (947.00 bytes)

   11   — NOTE: replace these accordingly

   12   — replace columns : MyEntityID, MyParentEntityID

   13   — replace parameter : @MyEntityIDParameter

   14   — replace table: MyTable

   15   — also note that the condition for anchor and recursive part

   16   — and the final consumption (select statement after the WITH block)

   17   — can be changed dependending on requirement

   18   WITH AncestorsAndSelf([ID], [ParentID], [Sequence]) AS

   19   (

   20       — self (anchor part)

   21       SELECT

   22         [ID] = MyEntityID,

   23         [ParentID] = MyParentEntityID,

   24         [Sequence] = 1

   25       FROM MyTable

   26       WHERE

   27         MyEntityID = @MyEntityIDParameter

   28 

   29       UNION ALL

   30 

   31       — ancestors (recursive part)

   32       SELECT

   33         [ID] = MyEntityID,

   34         [ParentID] = MyParentEntityID,

   35         [Sequence] = [Sequence] + 1

   36       FROM AncestorsAndSelf AS CTE, MyTable AS T1

   37       WHERE CTE.[ParentID] = T1.MyEntityID

   38   )

   39 

   40     SELECT *

   41     FROM AncestorsAndSelf

Download above code as text file > CTE_ancestorsAndSelf.txt (939.00 bytes)

Please let me know if I missed anything.

Hope you and me could find this useful someday.