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);
CodePudding user response:
OK, one possible way you could do it, not at all sure its the best way though.
- Create a new column
pathid
based on the same principle as your existingpath
(name), but being unique per person. - Count how many times that
id
shows up inpathid
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 |