Query hierarchical data in SQL Server

Question

I have a table called SegmentCaller which saves a segment code (SegmentCaller) and the segment codes it calls (SegmentCalled), structured as below

SegmentCaller	SegmentCalled
SEG1	SEG2
SEG1	SEG3
SEG1	SEG4
SEG2	SEG5
SEG2	SEG6
SEG3	SEG7
SEG4	SEG8
SEG7	SEG9
SEG8	SEG1
SEG9	SEG7

Here is an graphical view of the data: Node graph

I need a query that will start with a segment code, for example SEG1 and continue looking for all descendants until all paths end (as illustrated in the graph above). This can go on for hundreds of levels downwards, and there is the issue of circular references, where, for example, SEG8 could call SEG1 again.

Tried using a CTE, but I am not that familiar with it. Since SegmentCaller has repeated rows for each segment, couldn't figure out how to make it work.

WITH tmp AS (
SELECT
    [SegmentCaller],
    [SegmentCalled]
FROM       
    [FOSAnalysis].[dbo].[FOSSegmentCaller]
WHERE SegmentCaller = 'SEG1'
UNION ALL
SELECT
    sc.[SegmentCaller],
    sc.[SegmentCalled]
FROM       
    [FOSAnalysis].[dbo].[FOSSegmentCaller] sc
INNER JOIN tmp o
    ON o.SegmentCalled = sc.SegmentCaller
) 
SELECT * FROM tmp

Using the sample code above, as seen in the table, some segments, as in the case of 2-9, are completely ignored instead of getting the descendants of each one. Also, the query above throws the following error.

The statement terminated. The maximum recursion 100 has been exhausted before statement completion.

SegmentCaller	SegmentCalled
SEG1	SEG2
SEG1	SEG3
SEG1	SEG4
SEG1	SEG5
SEG1	SEG6
SEG1	SEG7
SEG1	SEG8
SEG1	SEG9
SEG1	SEG10
SEG10	SEG11
SEG10	SEG12
SEG10	SEG13
SEG13	SEG14
SEG13	SEG15
SEG15	SEG16
SEG14	SEG16

Please include the code you have so far, even if it's not working correct or has errors. — Stu, May 20 '22 at 20:54
Also, include sample data for the circular references you mentioned. — Dan Guzman, May 20 '22 at 21:06
Thanks for the feedback. Just updated the original question with some additional details as the sample query I tried using and the result, as well as an example of a simple circular reference that may be encountered. — Neon D, May 20 '22 at 23:18
If it can go for hundreds of levels deep then the recursion limit of 100 is going to be a problem. You will need to set the max recursion option. https://learn.microsoft.com/en-us/sql/t-sql/queries/with-common-table-expression-transact-sql?view=sql-server-ver15 You will still need to find a way to prevent circular references, which will require storing or querying the already-found data — stephen.vakil, May 21 '22 at 00:24
In the case of a cycle, what do you want to happen? E.g. SEG1 → SEG4 → SEG8 → SEG1..., what would you like in your result set for any/all of those rows? — Ben Thul, May 21 '22 at 16:07
Ideally, an additional column to indicate that the segment has already appeared. For example if SEG1 reappears, save a row for it and have a column that indicates it had appeared before. — Neon D, May 21 '22 at 17:11

stephen.vakil · Answer 1 · 2022-05-20T21:59:45.020

You will need to figure out a means of preventing circular references from being traversed. Otherwise a CTE structured similar to the below should work. In this example, I adapted this answer and added a path to the traversal, and excluded any rows from traversal that contained a segment that had already been traversed.

CREATE TABLE Segments (
  SegmentCaller VARCHAR(4),
  SegmentCalled VARCHAR(4)
);

INSERT INTO Segments
  (SegmentCaller, SegmentCalled)
VALUES
  ('SEG1', 'SEG2'),
  ('SEG1', 'SEG3'),
  ('SEG1', 'SEG4'),
  ('SEG2', 'SEG5'),
  ('SEG2', 'SEG6'),
  ('SEG3', 'SEG7'),
  ('SEG4', 'SEG8'),
  ('SEG7', 'SEG9'),
  ('SEG9', 'SEG3');
  
  WITH items AS (
    SELECT SegmentCaller, SegmentCalled
    , 0 AS Level
    , CAST(CAST(SegmentCaller AS VARCHAR(255))  + '.' + CAST(SegmentCalled AS VARCHAR(255)) AS VARCHAR(255)) AS  Path
    FROM Segments 
    WHERE SegmentCaller = 'SEG1'

    UNION ALL

    SELECT i.SegmentCaller, i.SegmentCalled
    , Level + 1
    , CAST(Path + '.' + CAST(i.SegmentCalled AS VARCHAR(255)) AS VARCHAR(255))
    FROM Segments i
    INNER JOIN items itms ON itms.SegmentCalled = i.SegmentCaller
    WHERE Path not like '%' + i.SegmentCalled + '%'
)

SELECT * FROM items ORDER BY Path

Result in:

SegmentCaller	SegmentCalled	Level	Path
SEG1	SEG2	0	SEG1.SEG2
SEG2	SEG5	1	SEG1.SEG2.SEG5
SEG2	SEG6	1	SEG1.SEG2.SEG6
SEG1	SEG3	0	SEG1.SEG3
SEG3	SEG7	1	SEG1.SEG3.SEG7
SEG7	SEG9	2	SEG1.SEG3.SEG7.SEG9
SEG1	SEG4	0	SEG1.SEG4
SEG4	SEG8	1	SEG1.SEG4.SEG8

Query hierarchical data in SQL Server

1 Answers1