question

EmekaOkoye-3272 avatar image
0 Votes"
EmekaOkoye-3272 asked OlafHelper-2800 answered

How to consume data returned from recurcieve CTE in SQL

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-general
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

1 Answer

OlafHelper-2800 avatar image
0 Votes"
OlafHelper-2800 answered

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
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.