Applying recursion to a SQL Server query

Question

The table in SQL Server has mapping of parent to child references of many generations.

I have to achieve result:

Level No || Child Count

So, I have following query for it :

SELECT 
    'L1' LEVEL, COUNT(ChildRef) CHILD 
FROM 
    [dbo].[TOMatriX] 
WHERE 
    ParentRef = 1

UNION ALL

SELECT 
   'L2' LEVEL, COUNT(ChildRef) CHILD 
FROM 
   [dbo].[TOMatriX]
WHERE 
   ParentRef IN (SELECT ChildRef FROM [dbo].[TOMatriX] WHERE ParentRef = 1)

UNION ALL

SELECT 
   'L3' LEVEL, COUNT(ChildRef) CHILD 
FROM 
   [dbo].[TOMatriX]
WHERE 
   ParentRef IN (SELECT ChildRef FROM [dbo].[TOMatriX]
                 WHERE ParentRef IN (SELECT ChildRef FROM [dbo].[TOMatriX] 
                                     WHERE ParentRef = 1)
                )

UNION ALL

SELECT 
   'L4' LEVEL, COUNT(ChildRef) CHILD 
FROM 
   [dbo].[TOMatriX]
WHERE 
   ParentRef IN (SELECT ChildRef FROM [dbo].[TOMatriX]
                 WHERE ParentRef IN (SELECT ChildRef FROM [dbo].[TOMatriX]
                                     WHERE ParentRef IN (SELECT ChildRef 
                                                         FROM [dbo].[TOMatriX] 
                                                         WHERE ParentRef = 1)
                                    )
                )

How can make this query dynamic & not hardcoded like this because levels can go above 4 also.

How to use Common Table Expressions or any recursion in this?

Answer 1

Say we have the following table:

create table #t1 (childref int, parentref int)
go
insert #t1 values
(1, null),
(2, 1),
(3, 1),
(4, 2),
(5, 2),
(6, 4),
(7, 4),
(8, 5),
(9, 6)
go

We can use a recursive CTE to go through the hierarchy and count the items for each level:

;with x as (
    select childref, 0 as lvl
    from #t1
    where parentref is null

    union all

    select #t1.childref, x.lvl+1
    from #t1
    inner join x on #t1.parentref = x.childref
)
select lvl, count(*)
from x
group by lvl

This is the result you wanted. Let's see how it works.

A recursive CTE has two parts (to make it simple): the anchor and the resursive part. The anchor:

    select childref, 0 as lvl
    from #t1
    where parentref is null

is executed once, and the result is temporarily stored. Then the recursive member is executed

    select #t1.childref, x.lvl+1
    from #t1
    inner join x on #t1.parentref = x.childref

See the inner join? 'x' here is actually a reference to whatever was a result of the anchor member. The column(s) we're joining on must be included in the select list.

The result of this step is stored, and is a base for the next step.

The recursive member is executed again, this time joining to the result of the previous step. And so on, as long as the recursive step returns any rows, or the maxrecursion is not reached.

UNION ALL here is just a syntax to connect these two together.

lvl is added to the selects to denote the current level, and is incremented on each step.

If you just select * from x after the CTE, you can see the raw output. What we did was to group it by lvl and count the rows.