TSQL CTE Recursive Query

Question

I'm trying to determine if I can create a treepath on some data in Microsoft SQL Server for a simple app that I'm creating. I have a single table that contains a [Site] column and a [RequestID] column. The [RequestID] column is a unique value, the Site is not (multiple requests per site). For example the data looks like this:

Site   RequestID
1      1234
1      1839
1      1934
2      2930
2      1029
3      2112

What I'm trying to do is create a query that would give me the treepath of each RequestID to site, for example using the above data:

Site   RequestID   Treepath
1      1234        1 -> 1234
1      1839        1 -> 1839
1      1934        1 -> 1934
2      2930        2 -> 2930
2      1029        2 -> 1029
3      2112        3 -> 2112

This would allow my application to list the Treepath in a treecontrol so per site you could see all the requests.

Here is an example of what I've been trying to get to work based on examples I've seen:

With CTE(site, RequstID, Parent, Level, Treepath) AS
(
SELECT 
    site
    ,RequestID
    ,Parent = TRIM(REPLACE(site, '.', ''))
    ,0 AS Level
    ,CAST(site AS VARCHAR(1024)) AS Treepath
FROM [dbo].[vwPendingRequests]

UNION ALL

SELECT 
    d.site
    ,d.RequestID
    ,Parent = TRIM(REPLACE(d.site, '.', ''))
    ,CTE.Level + 1 AS Level
    ,CAST(CTE.treepath + '->' + CAST(d.RequestID AS VARCHAR(1024)) AS VARCHAR(1024)) AS treepath
FROM [dbo].[vwPendingRequests] d
INNER JOIN CTE ON CTE.Parent = d.site
)
SELECT * FROM CTE

Snippet of results which are duplicating the RequestID twice per Site (notice in the snippet RequestID ending in 9924 is listed twice):

Site    RequestID   Parent  Level   Treepath
 1226.  0816201915385157492          1226.  0    1226.
 1378.  0819201914290965454          1378.  0    1378.
 1378.  0819201914301050727          1378.  0    1378.
 1170.  0812201910311259924          1170.  1    1170. -> 0812201910311259924      
 1170.  0816201914394436145          1170.  1    1170. -> 0816201914394436145      
 1170.  081620191529504974           1170.  1    1170. -> 081620191529504974       
 1170.  0816201916175658845          1170.  1    1170. -> 0816201916175658845      
 1170.  0816201916214766144          1170.  1    1170. -> 0816201916214766144      
 1170.  0816201916245247679          1170.  1    1170. -> 0816201916245247679      
 1170.  0812201910311259924          1170.  1    1170. -> 0812201910311259924

Ok, if I tweak it like this, it seems to work, just not sure if this is the most efficient way to write this. I have initially 12 requests, so I would expect 24 results:

With CTE(site, RequstID, Parent, Level, Treepath) AS
(
SELECT 
    site
    ,RequestID
    ,Parent = TRIM(REPLACE(site, '.', ''))
    ,0 AS Level
    ,CAST(site AS VARCHAR(1024)) AS Treepath
FROM [dbo].[vwPendingRequests]

UNION ALL

SELECT 
    d.site
    ,d.RequestID
    ,Parent = TRIM(REPLACE(d.site, '.', ''))
    ,CTE.Level + 1 AS Level
    ,CAST(CTE.treepath + '->' + CAST(d.RequestID AS VARCHAR(1024)) AS VARCHAR(1024)) AS treepath
FROM [dbo].[vwPendingRequests] d
INNER JOIN CTE ON CTE.Parent = d.site
)
SELECT * FROM CTE order by Level, Parent

Results:

Site    RequestID   Parent  Level   Treepath
 1170.  0812201910311259924          1170.  0    1170.
 1170.  0816201914394436145          1170.  0    1170.
 1170.  081620191529504974           1170.  0    1170.
 1170.  0816201916175658845          1170.  0    1170.
 1170.  0816201916214766144          1170.  0    1170.
 1170.  0816201916245247679          1170.  0    1170.
 1183.  081620191614426065           1183.  0    1183.
 1185.  0816201916093524374          1185.  0    1185.
 1202.  0816201915425031255          1202.  0    1202.
 1226.  0816201915385157492          1226.  0    1226.
 1378.  0819201914290965454          1378.  0    1378.
 1378.  0819201914301050727          1378.  0    1378.
 1170.  081620191529504974           1170.  1    1170. -> 081620191529504974       
 1170.  0816201914394436145          1170.  1    1170. -> 0816201914394436145      
 1170.  0812201910311259924          1170.  1    1170. -> 0812201910311259924      
 1170.  0816201916245247679          1170.  1    1170. -> 0816201916245247679      
 1170.  0816201916214766144          1170.  1    1170. -> 0816201916214766144      
 1170.  0816201916175658845          1170.  1    1170. -> 0816201916175658845      
 1183.  081620191614426065           1183.  1    1183. -> 081620191614426065       
 1185.  0816201916093524374          1185.  1    1185. -> 0816201916093524374      
 1202.  0816201915425031255          1202.  1    1202. -> 0816201915425031255      
 1226.  0816201915385157492          1226.  1    1226. -> 0816201915385157492      
 1378.  0819201914301050727          1378.  1    1378. -> 0819201914301050727      
 1378.  0819201914290965454          1378.  1    1378. -> 0819201914290965454

If you edit your question to include your attempt we can help you troubleshoot it.DDL also helps. — HABO, Aug 23 '19 at 17:20
Oops, thanks, thought I had included that. Pasted in my code. — Mike, Aug 23 '19 at 17:28
The _anchor_ query (first `select`) doesn't have a `where` clause. That isn't fatal, but usually only a subset of the rows are considered "parents" or some other starting point. The _recursive_ query (second `select`) is usually `... from CTE inner join vwPendingRequests as PR on PR.SomeCol = CTE.SomeOtherCol ...`. That ties the most recently selected rows (`CTE`) together with the base table (or view) (`vwPendingRequests`) to create the next set of rows recursively. [This](https://stackoverflow.com/a/15081353/92546) answer has a self-contained example. — HABO, Aug 23 '19 at 18:02
Not sure what I could use as a predicate given that it's just Site and Request ID. I did have the inner join but in my various attempts dropped that. Edited my example to include the correct code and results. As you can see I'm getting more the same requestID listing twice for the site. It's close, if I can get the duplicate requests removed. — Mike, Aug 23 '19 at 18:09
I'm sorry, but I don't see the need for a recursive cte here. Your data isn't hierarchical so why would you want a recursion here? — Zohar Peled, Aug 25 '19 at 08:25

TSQL CTE Recursive Query

0 Answers0