title: Is there a way to create a groupID for a recursive CTE in SSMS?
body: <p>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:</p>
  <pre><code>rootID RootName RelatedName
  1   ABA GPS
  1   ABA PIG
  1   ABA BBY
  1   ABA PIG
  2   PIG DDS
  2   PIG GPS
  </code></pre>
  <p>What I'm trying to achieve is a group ID column in which the data may look like this:</p>
  <pre><code>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
  </code></pre>
  <p>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?</p>
  <pre class="lang-sql prettyprint-override"><code>;WITH cte_Rel AS (
  SELECT
  &lt;columns&gt;
  FROM #RawRel r 
  WHERE 1 = 1
  AND &lt;initial Conditions&gt;
  UNION ALL 
  SELECT
  &lt;Columns&gt;
  FROM #RawRel r
  JOIN cte_Rel c ON r.RootName = c.RelatedName
  ) 
  SELECT DISTINCT * FROM cte_Rel
  OPTION (MAXRECURSION 100)
  </code></pre>