I have the following table:
pairID source_issue_id destination_issue_id
1 J I
2 B C
3 J M
4 F I
5 A B
6 A E
7 N O
8 J L
9 C D
10 P Q
11 G H
12 B F
13 L K
14 C N
15 A G
16 E F
representing nodes in a graph. I'm not using pairID, since does not seem useful.
I want to get the ancestor path for all nodes, the level at which each pair occurs, the full path, and the 'path group'
So far I've used the following code:
;with auxPairs as (
select 1 as lvl, b.source_issue_id, b.destination_issue_id, cast((b.source_issue_id+ '|' + b.destination_issue_id) as varchar(50)) as "full_path"
from Pairs2 b
where
b.source_issue_id not in ( select destination_issue_id from Pairs2)
union all
select lvl+1 as lvl, c.source_issue_id, c.destination_issue_id,
CAST((a.full_path + '|' + c.destination_issue_id) as varchar(50)) as "full_path"
from Pairs2 c
join auxPairs a on a.destination_issue_id = c.source_issue_id
)
that gives me the 'coection level' of two nodes (e.g. A|B is level 1) in "lvl", the source and destination nodes and the full path (up to that pair), e.g.
lvl source destination full_path
1 A B A|B
2 B C A|B|C
3 C N A|B|C|N
4 N O A|B|C|N|O
1 A B A|B
2 B C A|B|C
3 C D A|B|C|D
....
and so on for each path in the tree.
I need to add to this a "Path_id" or "group_id" so I get:
path_id lvl source destination full_path
1 1 A B A|B
1 2 B C A|B|C
1 3 C N A|B|C|N
1 4 N O A|B|C|N|O
2 1 A B A|B
2 2 B C A|B|C
2 3 C D A|B|C|D
....
meaning that the nodes with the same path_id are coected in a given order
NOTE: The alphabetical 'fake" order will not work with actual data. I need to use the path_id and lvl to establish the partial order within the path