There are a number of cases in programming where you are often forced to store data in one format but need to convert that data to a different format for display.  One of the situations where this seems to come up quite often is in the storage hierarchical data structures.  SQL Server has traditionally had very poor support for dealing with hierarchical data.  SQL 2005 and 2008 have made some effort to try and simplify solving this problem, unfortunately, there is still a lot of data that is stored in a format that is not readily able to take advantage of these new SQL Features.

When working with legacy data, or legacy business tiers which expose the data as flat collections with ID and ParentID fields, you will likely need a simple way to convert these records into some form of nested collection.  This is a problem where people often resort to brute force methods for doing this conversion.  Unfortunately, the brute force approach is often slow and very specific to a specific set of data.

More...