SQL server recursion path id

Question

When using recursion with CTE in SQL Server, is there a way to reveal the path id SQL server is using ?

Let's say I have the following table representing edges connected to each other:

CREATE TABLE c(id int, n1 int, n2 int);

insert into c( id, n1, n2 ) values ( 100, 1, 2);
insert into c( id, n1, n2 ) values ( 101, 2, 3 );
insert into c( id, n1, n2 ) values ( 102, 3, 4 );
insert into c( id, n1, n2 ) values ( 103, 4, 8 );
insert into c( id, n1, n2 ) values ( 104, 3, 11 );
insert into c( id, n1, n2 ) values ( 105, 11, 12 );

I can now use the following recursive query to show what nodes are connected to each other :

WITH Nodes
AS
(   
    select c.id, c.n1, c.n2, 1 as level from c where id = 100
    UNION ALL   
    select c.id, c.n1, c.n2, level+1 from c inner join Nodes on Nodes.n2 = c.n1 where c.id != 100
)
SELECT id, n1, n2, level from Nodes where level > 1 order by id;

This will give the following result :

id  n1  n2  level
101 2   3   2
102 3   4   3
103 4   8   4
104 3   11  3
105 11  12  4

There are 2 paths, 101-102-103 & 101-104-105. I want to identify each path, but I think they actually are equal to the recursion paths SQL Server is using, so if I have a way of getting this recursion path id, then I can identify each unique path.

I want my output to look like this :

id  n1  n2  level path_id
101 2   3   2     1
102 3   4   3     1
103 4   8   4     1
104 3   11  3     2
105 11  12  4     2

As you can see, at node 104 a second path or recursion is encountered. These are the nodes making up the paths :

path 1 : 101 - 102 - 103
path 2 : 104 - 105

Each path actually starts at node 100, but it is left out of the results by the 'level>1' clause.

Thanks, Steef

Answer 1

If I understand you correctly, you are looking for something like this.

Data

CREATE TABLE c(id int, n1 int, n2 int);

insert into c( id, n1, n2 ) values ( 100, 1, 2);
insert into c( id, n1, n2 ) values ( 101, 2, 3 );
insert into c( id, n1, n2 ) values ( 102, 3, 4 );
insert into c( id, n1, n2 ) values ( 103, 4, 8 );
insert into c( id, n1, n2 ) values ( 104, 3, 11 );
insert into c( id, n1, n2 ) values ( 105, 11, 12 );

Query

;WITH Nodes
AS
(   
    select c.id, c.n1, c.n2, 1 as level ,convert(varchar(1000),convert(varchar(1000),id) + '/') as pid
    from c where id = 100
    UNION ALL   
    select c.id, c.n1, c.n2, level+1,convert(varchar(1000),nodes.pid + convert(varchar(1000),c.id) + '/') from c inner join Nodes on Nodes.n2 = c.n1 where c.id != 100
)
SELECT id, n1, n2, level,pid from Nodes where level > 1 order by id;

The query shows the complete path traversed will the current point by appending the current id to the previous nodes in the column pid. your output required is not very clear, if you can detail the exact output required in the question, I can update my answer accordingly.

Edit As per your output, you can do something like this.

;WITH Nodes
AS
(
    select c.id, c.n1, c.n2, 1 as level ,convert(int,1) as pid
    from c where id = 100
    UNION ALL   
    select c.id, c.n1, c.n2, level+1,convert(int,pid + ROW_NUMBER()OVER(ORDER BY (SELECT 1)) - 1) pid 
    from c inner join Nodes on Nodes.n2 = c.n1 where c.id != 100
)
SELECT id, n1, n2, level,pid from Nodes where level > 1 order by id;