Is there a way to create a groupID for a recursive CTE in SSMS?

Question

I'm building a query that outputs an ownership hierarchy for each root in my database. I'm using a recursive CTE with success in that I can achieve the following data output currently:

rootID RootName RelatedName
1   ABA GPS
1   ABA PIG
1   ABA BBY
1   ABA PIG
2   PIG DDS
2   PIG GPS

What I'm trying to achieve is a group ID column in which the data may look like this:

GroupID rootID  RootName RelatedName
100    1    ABA GPS
100    1    ABA PIG
100    1    ABA BBY
100    1    ABA PIG
100    2    PIG DDS
100    2    PIG GPS

and likewise for group 200, 300,...etc. for each tree. What part of the recursive CTE can code be injected such to achieve the above result?

;WITH cte_Rel AS (
SELECT
<columns>
FROM #RawRel r 
WHERE 1 = 1
AND <initial Conditions>
UNION ALL 
SELECT
<Columns>
FROM #RawRel r
JOIN cte_Rel c ON r.RootName = c.RelatedName
) 
SELECT DISTINCT * FROM cte_Rel
OPTION (MAXRECURSION 100)

Where does the value of `GroupID` come from? As it's all the same value, why not simple `100 AS GroupID`? — Thom A, Sep 23 '20 at 15:24
There would be multiple trees in my data... So in the demo above, the CTE will generate this tree 100, but also there are may other trees that would require their own groupID. — SQALEX101, Sep 23 '20 at 15:37
So I would need a clever incrementing strategy in this CTE or something... — SQALEX101, Sep 23 '20 at 15:38

score 0 · Answer 1 · answered Sep 26 '20 at 21:52

You can add a row number to the anchor part of the recusive CTE. Multiply by 100 and repeat the same column in the second part of the CTE.

Fiddle in case you prefer interactive code.

Sample data

Without your actual query and sample input data it is hard to perfectly replicate your current output so I generated my own sample data.

create table RelData
(
  ParentId int,
  Id int,
  Name nvarchar(3)
);

insert into RelData (ParentId, Id, Name) values
(null, 1, 'A00'), -- tree A
(1,    2, 'A10'),
(2,    3, 'A11'),
(2,    4, 'A12'),
(1,    5, 'A20'),
(5,    6, 'A21'),
(null, 7, 'B00'), -- tree B
(7,    8, 'B10'),
(8,    9, 'B11');

Solution

WITH cte_Rel AS (
  SELECT row_number() over(order by rd.Id) * 100 as TreeId,  -- number to roots and multiply the root number by 100
         rd.Id, rd.Name, rd.ParentId, convert(nvarchar(3), null) as ParentName
  FROM RelData rd
  WHERE rd.ParentId is null
  
    UNION ALL
  
  SELECT c.TreeId,  -- repeat the tree number
         rd.Id, rd.Name, rd.ParentId, c.name
  FROM RelData rd
  JOIN cte_Rel c ON rd.ParentId = c.Id
) 
SELECT c.TreeId, c.ParentId, c.ParentName, c.Name
FROM cte_Rel c
where c.ParentId is not null
order by c.ParentId;

Result

TreeId ParentId ParentName Name
------ -------- ---------- ----
100    1        A00        A10
100    1        A00        A20
100    2        A10        A11
100    2        A10        A12
100    5        A20        A21
200    7        B00        B10
200    8        B10        B11

Is there a way to create a groupID for a recursive CTE in SSMS?

1 Answers1