Ivan Uzunov Blog

Common table expressions (CTEs) are a really cool feature of MS SQL 2005. They let you create queries that work with recursive data and queries that are simpler and therefore more readable. You can use CTE-based recursive queries instead of cursors.

To create a recursive CTE, you must perform the following steps:

1. Create the anchor member query.

   You must create the anchor member query first. This forms the top of the recursion tree and defines the column names unless you specify them in the optional column_list parameter to the WITH statement.

2. Add the UNION ALL operator.

   The UNION ALL operator is necessary to link the two result sets together. This is the only set operator allowed in a recursive query.

3. Create the recursive member query.

   The recursive member query must reference the CTE as one of the tables in the SELECT query. In addition, the number of columns must match the number of those in the anchor member. The recursion continues until this member query ceases to produce result sets.

 
Getting Record Hierarchy Level Using CTE’s

WITH cteCategoryHierarchy
AS
(
    — THIS IS ANCHOR MEMBER QUERY
    SELECT    id as category_id,
            category_name,
            parent_category_id,
            1 AS hierarchy_level
    FROM dbo.Categories
    WHERE (parent_category_id IS NULL)
   
    UNION ALL
   
    — THIS IS RECURSIVE MEMBER QUERY
    SELECT  C.id AS category_id,
            C.category_name,
            C.parent_category_id,
            CH.hierarchy_level + 1 AS hierarchy_level
    FROM dbo.Categories AS C INNER JOIN
              cteCategoryHierarchy AS CH ON C.parent_category_id = CH.category_id
)

SELECT *
FROM  cteCategoryHierarchy
ORDER BY hierarchy_level

 
Get all parent records list with CTE

WITH cteParentCategories (category_id, parent_category_id)
AS
(
        SELECT id as category_id,
            parent_category_id
        FROM Categories
        WHERE parent_category_id IS NOT NULL
      
        UNION ALL
      
        SELECT C.id AS category_id, ctePC.parent_category_id
        FROM dbo.Categories AS PC INNER JOIN
            cteParentCategories AS ctePC ON C.parent_category_id = ctePC.category_id
)

SELECT * FROM cteParentCategories
ORDER BY category_id, parent_category_id

Trackback URI | Comments RSS