How to consume data returned from recurcieve CTE in SQL

Emeka Okoye 86 Reputation points
2021-09-23T11:16:26.993+00:00

I have a table in sql db that looks like this:

Matrix Table
Id Child Parent Level
----+-----------+-----------+----------
1 Q-00001 Q-00000 NULL
2 Q-00002 Q-00001 NULL
3 Q-00003 Q-00001 NULL
4 Q-00004 Q-00002 NULL
5 Q-00005 Q-00002 NULL
6 Q-00006 Q-00003 NULL
7 Q-00007 Q-00003 NULL
8 Q-00008 Q-00004 NULL
9 Q-00009 Q-00005 NULL
10 Q-00010 Q-00006 NULL
11 Q-00011 Q-00007 NULL
12 Q-00012 Q-00005 NULL
13 Q-00013 Q-00004 NULL

and the out come looks like this:

Expected Result:
Child Parent Level
-----------+----------+---------
Q-00002 Q-00001 1
Q-00003 Q-00001 1
Q-00004 Q-00001 2
Q-00005 Q-00001 2
Q-00006 Q-00001 2
Q-00007 Q-00001 2
Q-00008 Q-00001 3
Q-00009 Q-00001 3
Q-00010 Q-00001 3
Q-00011 Q-00001 3
Q-00012 Q-00001 3
Q-00013 Q-00001 3

the sql query is as below:
;WITH downlines(Child,Parent,Level)
AS (
SELECT
Child,
Parent,
1
FROM
[Matrix2]
WHERE Parent = 'Q-00001'
UNION ALL
SELECT
m.Child,
d.Parent, d.level +1
FROM
downlines d
INNER JOIN Matrix2 m
ON d.Child = m.Parent
)
SELECT * FROM downlines
ORDER BY Child,Parent, Level
;

My question is how do I modify the above query to be able to do further task when the CTE table is returned.
I want to do a task like,
SELECT COUNT(Level) FROM (CTE table) WHERE Level = 2

SQL Server
SQL Server
A family of Microsoft relational database management and analysis systems for e-commerce, line-of-business, and data warehousing solutions.
13,343 questions
0 comments No comments
{count} votes

Accepted answer
  1. Olaf Helper 43,166 Reputation points
    2021-09-23T12:33:12.03+00:00

    Simply the same way as in your example query =>

    ;WITH downlines(Child,Parent,Level)
    AS (
    SELECT
    Child,
    Parent,
    1
    FROM
    [Matrix2]
    WHERE Parent = 'Q-00001'
    UNION ALL
    SELECT
    m.Child,
    d.Parent, d.level +1
    FROM
    downlines d
    INNER JOIN Matrix2 m
    ON d.Child = m.Parent
    )
    SELECT COUNT(Level) FROM downlines WHERE Level = 2
    
    0 comments No comments

0 additional answers

Sort by: Most helpful