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.