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)
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
FROM tree
ORDER BY pathstr, id
Denny Lee 