I'm stuck in a problem that continuously hammering in my mind to find the solution.
I have a users table with 6k of rows and they are related as parent-child. The problem is that I want results not only at a single level parent-child relationship but at all levels (until the last child) for every userid.
I have this data in my users table.
| Tbl_Users |
| UserID |
Parent ID |
| 1 |
0 |
| 2 |
1 |
| 3 |
1 |
| 4 |
2 |
| 5 |
2 |
| 6 |
5 |
| 7 |
6 |
| 8 |
6 |
| 9 |
0 |
I want this data as the output of CTE.
| Output |
| Parent ID |
UserID |
| 1 |
1 |
| 1 |
2 |
| 1 |
3 |
| 1 |
4 |
| 1 |
5 |
| 1 |
6 |
| 1 |
7 |
| 1 |
8 |
| 2 |
2 |
| 2 |
4 |
| 2 |
5 |
| 2 |
6 |
| 2 |
7 |
| 2 |
8 |
| 3 |
3 |
| 4 |
4 |
| 5 |
5 |
| 5 |
6 |
| 5 |
7 |
| 5 |
8 |
| 6 |
6 |
| 6 |
7 |
| 6 |
8 |
| 7 |
7 |
| 8 |
8 |
| 9 |
9 |
Note: The ParentID includes not only its children but also children of its children and itself too. I'm using MSSQL 2019.