title: SQL Server 2005 CTE hierachical table, managing duplicates, without maxrecursion
body: <p>The problem I am attempting to solve is a request to pull data from a table using a CTE to retrieve hierarchical data. Because of the nature of the data referencing itself, hitting max recursion is a huge problem but I don't want to arbitrarily stop the data because then it wouldn't be complete. To understand the data, here is what the sample output should look like: </p>
  
  <p>Top Level</p>
  
  <pre><code>11552
       - 11414
       - 68775
              - 23667
                     - 65887*
              - 58669*
       - 58669
              - 65887*
  
  11414
       - 58667
              - 11552*
       - 45998
              - 23667
                     - 65887*
              - 33789
       - 26998
              - 11552*
  65887
       - 26988
              - 23667
                     - 65887*
              - 45998*
       - 58667
              - 11552*
  </code></pre>
  
  <p>The problem lies in not repeating the chain for items that have already appeared in the hierarchy but still including at least that first value (items marked by asterisk). </p>
  
  <p>UPDATED per request:
  The data in the table is structured like this: </p>
  
  <pre><code>Id   ProcId   AsscId
  1    11552    11414
  2    11552    68775
  3    11552    58669
  4    68775    23667
  5    68775    58669
  6    23667    65887
  7    58669    65887
  8    11414    58667
  9    11414    45998
  10   11414    26998
  11   58667    11552
  12   45998    23667
  13   45998    33789
  14   26998    11552
  15   65887    26998
  16   65887    58667
  17   26988    23667
  18   26988    45998 
  </code></pre>
  
  <p>My initial CTE query (which includes a stop for now) is below. The query only pulls the initial data but I can't figure out how to alter it to meet the request. </p>
  
  <pre><code>WITH myCTE AS 
  (   
          SELECT ProcId, AsscId, 0 as [Level]
          FROM Associations 
  
          UNION ALL
  
          SELECT a.ProcId, a.AsscId, ([Level] + 1) as RecursionLevel
          FROM Associations a
          INNER JOIN myCTE m ON a.ProcId = m.ProcId
          WHERE ([Level] + 1) &lt;= 2
  )
  SELECT *  
  FROM myCTE 
  ORDER BY ProcId
  </code></pre>
  
  <p>I looked at these samples here <a href="https://stackoverflow.com/questions/2750709/how-to-use-common-table-expression-and-check-no-duplication-in-sql-server">How to use Common Table Expression and check no duplication in SQL Server</a> and here <a href="https://stackoverflow.com/questions/22555408/removing-duplicates-from-a-cte-based-on-a-specific-criteria">Removing duplicates from a CTE based on a specific criteria</a> but I am not certain either helps in this scenario. </p>
  
  <p>POTENTIAL SOLUTION: </p>
  
  <p>So after further searching and some discussion, I learned that in this scenario a CTE really isn't the best choice. Instead, I'm creating a temp table, filling it with the first row of parent data, then calling a separate query that goes to get the additional data. If that query finds there is more data it calls itself again and gets the next level of data. When it goes to get the next level of data it only goes to retrieve that data that does not already exist in the temp table. The temp table holds the value of the ProcId, the parent id, the level on which it was retrieved and a column that holds a boolean value if the id already exists in the temp table. The boolean value will be used indicate if the full data will display or not. </p>