Recursive Query

You might end up in writing long queries to do the recursion calling in your query. I see many people uses the cursor and do the magic in their code. But there is a better way to do that, "Recursive Query". Normally you use the recursive query to build hierarchical data. For e.g. say you have a table where the data has parent child relationship, and you use parent_id column to maintain the relationship. One or the other point of time, you may require to build a hierarchical structure with relative levels. Let me put the query so that you can easily understand.

I have a table with group_id, parent_group_id, group_name. I want to retrieve the tree structure for the given group. The below is the simple recursive query that does the magic.

-- declare the name of the recursive table
WITH GroupCTE (group_id,parent_group_id, group_name, relative_level)
AS
(
    -- you retrieve the first level (0th level)
    SELECT 
        group_id,
        parent_group_id,
        group_name,
        0 -- start relative level as 0
    FROM groups
        WHERE group_id = @group_id
        UNION ALL -- union with the recursion
        
        SELECT 
            R1.group_id,
            R1.parent_group_id,
            R1.name,
            R2.relative_level + 1
        FROM groups AS R1 -- your table
        JOIN GroupCTE AS R2 ON 
            R1.parent_group_id = R2.group_id -- join with recursive table
)
--finally return the values
SELECT * FROM RecursionCTE

This is a simple example, you may want to refer the MSDN further on this topic.