Anuj @ ColdFusion, Flex and some more….

I was recently stuck with a problem where I had to loop over a self referencing table (unlimited parent child relationships from the same table) and generate a tree structure upto n levels and because my table didnt have the levels defined in it, the tree leveles were to be defined at run time. What most people would do in this case is start from parent level and loop over until you reach the last level, this loop can be either in your scripting language (CF in my case) or a recursive stored procedure or a really complex query. I would have done the same if I didnt come across Common Table Expressions (CTE’s) which were introduced in Sql Server 2005.

A CTE is a better form of a derived table that needs to be declared only once and can be referenced or recursively defined multiple times in a query.  Consider a scenario with the following data :-

Now with the above example, if you wanted to generate a hierarchical dataset with everything in it, and an additional column called [Level] that would tell you at what level the category is, its pretty easy to do so with a CTE. Here is how it looks like :-

So as you can see, its pretty easy to understand this code.  A CTE can be considered as another form of View but with more capabilities. In the above query, I start off the query by giving a name to the CTE (CatTree), and then assigning the columns I want it to have. Notice, the column [Level] is not in the actual table, its a derived column which is computed as part of the recursive CTE call.

The trick is that after the CTE query, you immediately need to fire a SELECT query which uses the CTE, otherwise the CTE is no longer available. This is a MUST. The select query should be immediately after the CTE.

Running the above query, gives a result like this.

So as you can see, the column [Level] has been computed and can now be used to generate n level of nested structures starting from any particular level or category in the tree.  It is quite handy when you can work with derived/computed columns all in one query.  If you are using ColdFusion, you could save this query in memory and then do a query of queries whereever you wanted to retrieve only certain level of categories etc… in your application.

Here is the some documentation on CTE’s for those who want to read on.