There are a number of things that SQL, for all of its good points, isn’t very good at; for example, FOR loops and expressions with horizontal references. One thing that it seems to be particularly bad at is dealing with hierarchical data (i.e. self-referencing tables with multiple levels of recursion). Fortunately, since SQL Server 2005, a very useful mechanism has existed for dealing with this kind of data: recursive common table expressions (CTEs).
In this example, I will show you how to produce a directory listing that shows full path names, given the following table structure:
(Obviously, this is a simplification; a useful folder representation would include columns for creation date, attributes, etc)
The generalised form for a recursive CTE is:
WITH [cte-name] as ( SELECT [select-list] FROM [table-name] UNION ALL SELECT [select-list] FROM [cte-name] JOIN [table-name] ) SELECT [select-list] FROM [cte-name]
You must include both a subquery for the anchored part of the CTE as well as a subquery that joins to the CTE itself. For our example, the anchored part would be the set of folders without a parent (i.e. ParentID IS NULL). The concatenatation of the folder name and the path above it is performed in the recursive subquery. The directory listing query looks like this:
WITH DirListing as ( SELECT FolderID, FolderName FROM Folders WHERE ParentID IS NULL UNION ALL SELECT Folders.FolderID, DirListing.FolderName+'\'+Folders.FolderName as [FolderName] FROM DirListing INNER JOIN Folders ON Folders.ParentID=DirListing.FolderID ) SELECT FolderName FROM DirListing ORDER BY FolderName
For the following sample data in Folders:
The directory listing query will produce the following output:
Fruit Fruit\Apples Fruit\Apples\Fuji Fruit\Apples\Golden Delicious Fruit\Apples\Granny Smith Fruit\Apples\Pink Lady Fruit\Oranges Fruit\Oranges\Blood Fruit\Oranges\Valencia Fruit\Peaches Meats Spices Vegetables Vegetables\Capsicums Vegetables\Capsicums\Green Vegetables\Capsicums\Red Vegetables\Capsicums\Yellow Vegetables\Carrots Vegetables\Celery Vegetables\Cucumbers (20 row(s) affected)
In addition to using this construct to list the paths, you can also use it to return the full path for a single FolderID, or to perform a reverse-lookup for a FolderID given the path name. This makes it very handy for creating a file system abstraction within a database system.