JohnBs personal blog

Introduced in SQL 2005, CTEs or Common Table Expressions are a pretty cool feature to use. Rather than using a temporary table, a CTE is able to create temporary tables on the fly and perform then performing recursive queries - perfect for building a tree heirarchy.

Take for example, the following code - a pretty normal pageid, parentid relationship exists between nodes (in this case pages within a CMS system).


WITH PageTree(pageid, pageparentid, level,pathstr)
AS
(
select
pageid, pageparentid, 0,cast(pageid as varchar(max)) from pages where pageparentid is null
UNION ALL
Select v.pageid, v.pageparentid, t.level +1 , t.pathstr + ',' + cast(v.pageid as varchar(max)) from pages v
INNER JOIN
PageTree t
ON
t.pageid = v.pageparentid
)

select pageid, pageparentid, level, pathstr
from pagetree
order by pathstr, pageid


Firstly, notice the WITH statement at the top that defines the CTE and the columns that are going to be in your resultant table. Then the initial ’seed’ row is retrieved before the recursion begins. Outside of the WITH statement is the SELECT statement that selects against the CTE. In this example, i’ve create a column named ‘pathstr’ which will contain a comma seperated list of the pages in their heirarchy. Here’s the results;