Recursive Query

Use of Recursive Commom Table Expression (CTE)
If you have a parent-child table, a common thing to do is to run a recursive query that can list out all of the parent-child path strings.
 
For example, if you have a table called Taxonomy, with the format / data below:
 
TaxonomyID   

ParentTaxonomyID   

TaxonomyName

2

1

Shoes

3

1

Accessories

20

2

Ladies

21

2

Men

22

2

Kids

 

And you will want to list out the values:
\Shoes
\Accessories
\Shoes\Ladies
\Shoes\Men
\Shoes\Kids
 
To do this, you can use Recursive Common Table Expressions (CTE) to quickly get you that query instead of doing a loop query.  To do this, the query below sets up the tree to store the parent-child table.
 
WITH tree (data, id, level, pathstr)                       
AS (SELECT TaxonomyName, TaxonomyID , 1, CAST(TaxonomyName AS VARCHAR(MAX))          
      FROM dbo.Taxonomy
     WHERE TaxonomyID = ParentTaxonomyID
     UNION ALL                              
    SELECT TaxonomyName, TaxonomyID, t.level + 1, t.pathstr + ‘\’ + TaxonomyName
      FROM dbo.Taxonomy V                    
      JOIN tree t
        ON t.id = V.ParentTaxonomyID
     where TaxonomyID <> ParentTaxonomyID)
And you can query the tree by:
 
SELECT SPACE(level) + data as data, id, level, pathstr
  FROM tree
 ORDER BY pathstr, id
 
 

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s