title: How to write a multidimensional MS SQL joins to return common, progressive data links
body: <p>I'm trying to work out an efficient way to identify common data points based on iterative multi-joins. For example:</p>
  
  <pre><code>INPUT
  -----
  ID1 ID2
  X   Y
  Y   Z
  Z   1
  A   B
  C   D
  1   A
  B   A
  </code></pre>
  
  <p>X joins to Y. Y joins to Z. Z joins to 1.
  Hence, X and 1 are ("common") joined through Y and Z, and so forth, to create the following output:</p>
  
  <pre><code>OUTPUT
  ------
  ID1 ID2 CommonKey
  X   Y   1
  Y   Z   1
  Z   1   1
  A   B   1
  C   D   2
  1   A   1
  B   A   1
  </code></pre>
  
  <p>If data points are interrelated in any way, current or future, they should be given the same "CommonKey"</p>
  
  <p>I've looked into using CTE's for this solution but have been unsuccessful so far. </p>