Recursive SQL query (parent-child) to include total downline records per record

I have a table that has a parent – child structure.

I managed to bring this structure sorted by creating a field called “Path” with the first name of the agent, so this query:

  WITH cte(PLID, sponsorid, firstname, lastname, Status, LEVEL, path) AS (SELECT
                            PLID, sponsorid, firstname, lastname, Status, 0 AS LEVEL, CAST(firstname AS VARCHAR(1000)) AS path
                            FROM TEST  WHERE PLID =1  UNION ALL
                            SELECT c.PLID, c.sponsorid, c.firstname, c.lastname, c.Status, cte. LEVEL + 1 AS LEVEL,
                            CAST((cte.path + '/' + c.firstname) AS VARCHAR(1000)) 
                            AS path FROM TEST c INNER JOIN cte ON c.sponsorid = cte.plid)
                            SELECT PLID, sponsorid, firstname, lastname, Status, LEVEL, path
                            FROM cte ORDER BY path ASC

…returns this, the treeview data:

+------+-----------+-----------+----------+--------+-------+-------------------------------------+
| PLID | SPONSORID | FIRSTNAME | LASTNAME | STATUS | LEVEL |                PATH                 |
+------+-----------+-----------+----------+--------+-------+-------------------------------------+
|    1 |         0 | Danielle  | Lipsin   |      1 |     0 | Danielle                            |
|    4 |         1 | Alissa    | Doe      |      1 |     1 | Danielle/Alissa                     |
|    2 |         1 | Charles   | Doe      |      1 |     1 | Danielle/Charles                    |
|    6 |         2 | Mark      | Doe      |      1 |     2 | Danielle/Charles/Mark               |
|    5 |         2 | Martin    | Doe      |      1 |     2 | Danielle/Charles/Martin             |
|    8 |         5 | Katy      | Perry    |      1 |     3 | Danielle/Charles/Martin/Katy        |
|    7 |         5 | Leo       | Messi    |      1 |     3 | Danielle/Charles/Martin/Leo         |
|    9 |         7 | Alex      | Doe      |      1 |     4 | Danielle/Charles/Martin/Leo/Alex    |
|   10 |         7 | Laureen   | Doe      |      1 |     4 | Danielle/Charles/Martin/Leo/Laureen |
|    3 |         1 | Michelle  | Doe      |      1 |     1 | Danielle/Michelle                   |
+------+-----------+-----------+----------+--------+-------+-------------------------------------+

I tried to do a nested select but it didn’t work. What is the best approach to include the total number of children on each record?

Expected Results:

+------+-----------+-----------+----------+--------+-------+-------------------------------------+---------------+
| PLID | SPONSORID | FIRSTNAME | LASTNAME | STATUS | LEVEL |                PATH                 | TotalDownline |
+------+-----------+-----------+----------+--------+-------+-------------------------------------+---------------+
|    1 |         0 | Danielle  | Lipsin   |      1 |     0 | Danielle                            |             9 |
|    4 |         1 | Alissa    | Doe      |      1 |     1 | Danielle/Alissa                     |             0 |
|    2 |         1 | Charles   | Doe      |      1 |     1 | Danielle/Charles                    |             7 |
|    6 |         2 | Mark      | Doe      |      1 |     2 | Danielle/Charles/Mark               |             0 |
|    5 |         2 | Martin    | Doe      |      1 |     2 | Danielle/Charles/Martin             |             4 |
|    8 |         5 | Katy      | Perry    |      1 |     3 | Danielle/Charles/Martin/Katy        |             0 |
|    7 |         5 | Leo       | Messi    |      1 |     3 | Danielle/Charles/Martin/Leo         |             2 |
|    9 |         7 | Alex      | Doe      |      1 |     4 | Danielle/Charles/Martin/Leo/Alex    |             0 |
|   10 |         7 | Laureen   | Doe      |      1 |     4 | Danielle/Charles/Martin/Leo/Laureen |             0 |
|    3 |         1 | Michelle  | Doe      |      1 |     1 | Danielle/Michelle                   |             0 |
+------+-----------+-----------+----------+--------+-------+-------------------------------------+---------------+

Thanks.

CREATE TABLE TEST (
    PLID int,
    sponsorid int,
    firstname nvarchar(50),
    lastname nvarchar(50),
    status int
);
INSERT INTO TEST VALUES (1,0,'Danielle', 'Lipsin', 1);
INSERT INTO TEST VALUES (2,1,'Charles', 'Doe', 1);
INSERT INTO TEST VALUES (3,1,'Michelle', 'Doe', 1);
INSERT INTO TEST VALUES (4,1,'Alissa', 'Doe', 1);
INSERT INTO TEST VALUES (5,2,'Martin', 'Doe', 1);
INSERT INTO TEST VALUES (6,2,'Mark', 'Doe', 1);
INSERT INTO TEST VALUES (7,5,'Leo', 'Messi', 1);
INSERT INTO TEST VALUES (8,5,'Katy', 'Perry', 1);
INSERT INTO TEST VALUES (9,7,'Alex', 'Doe', 1);
INSERT INTO TEST VALUES (10,7,'Laureen', 'Doe', 1);

Answer

OK, one possible way you could do it, not at all sure its the best way though.

1. Create a new column pathid based on the same principle as your existing path (name), but being unique per person.
2. Count how many times that id shows up in pathid other than ours by using a sub-query against the CTE.

WITH cte1 (PLID, sponsorid, firstname, lastname, [Status], [LEVEL], [path], pathid) AS (
    SELECT PLID, sponsorid, firstname, lastname, [Status], 0 AS [LEVEL]
      , CAST(firstname AS VARCHAR(1000)) AS [path]
      , CAST('/' + CAST(PLID AS varchar(38)) AS varchar(max)) AS pathid
    FROM #TEST
    WHERE PLID = 1--94
    UNION ALL
    SELECT c.PLID, c.sponsorid, c.firstname, c.lastname, c.[Status], cte1.[LEVEL] + 1 AS [LEVEL]
      , CAST((cte1.[path] + '/' + c.firstname) AS VARCHAR(1000)) AS [path]
      , CAST(cte1.pathid + '/' + cast(c.PLID AS varchar(38)) AS varchar(max)) AS pathid
    FROM #TEST c
    INNER JOIN cte1 ON c.sponsorid = cte1.plid
)
SELECT PLID, sponsorid, firstname, lastname, [Status], [LEVEL], [path], pathid
    , (select count(*) from cte1 B where B.pathid + '/' like '%/' + cast(A.PLID AS varchar(38)) + '/%' and B.PLID <> A.PLID)
FROM cte1 A
ORDER BY [path] ASC;

Returns for your sample data:

PLID	sponsorid	firstname	lastname	Status	LEVEL	path	pathid	Total Downline
1	0	Danielle	Lipsin	1	0	Danielle	/1	9
4	1	Alissa	Doe	1	1	Danielle/Alissa	/1/4	0
2	1	Charles	Doe	1	1	Danielle/Charles	/1/2	6
6	2	Mark	Doe	1	2	Danielle/Charles/Mark	/1/2/6	0
5	2	Martin	Doe	1	2	Danielle/Charles/Martin	/1/2/5	4
8	5	Katy	Perry	1	3	Danielle/Charles/Martin/Katy	/1/2/5/8	0
7	5	Leo	Messi	1	3	Danielle/Charles/Martin/Leo	/1/2/5/7	2
9	7	Alex	Doe	1	4	Danielle/Charles/Martin/Leo/Alex	/1/2/5/7/9	0
10	7	Laureen	Doe	1	4	Danielle/Charles/Martin/Leo/Laureen	/1/2/5/7/10	0
3	1	Michelle	Doe	1	1	Danielle/Michelle	/1/3	0