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