title: Identifying sets of linked IDs in SQL Server
body: <p>I have a simple table that looks like this:</p>
  
  <pre><code>ClientID    ItemID
  1           1
  1           2
  1           3
  2           1
  2           2
  3           3
  4           3
  5           1
  5           2
  5           4
  5           5
  </code></pre>
  
  <p>where both columns combine to be the primary key. I am now tasked with identifying all the unique sets of ItemIDs assigned to ClientIDs. So in my example, the sets would be:</p>
  
  <pre><code>ItemIDs 1,2,3 (used by ClientID 1)
  ItemIDs 1,2 (used by ClientID 2)
  ItemIDs 3 (used by ClientIDs 3 and 4)
  ItemIDs 1,2,4,5 (used by ClientID 5)
  </code></pre>
  
  <p>Ideally the output would be two tables:</p>
  
  <pre><code>SetID    ItemID
  1        1
  1        2
  1        3
  2        1
  2        2
  3        3
  4        1
  4        2
  4        4
  4        5
  
  ClientID    SetID
  1           1
  2           2
  3           3
  4           3
  5           4
  </code></pre>
  
  <p>where SetID would be a new field for use elsewhere.</p>
  
  <p>Currently the way I have of identifying the unique sets involves using a cursor to build a string of the ordered ItemIDs for each ClientID, then comparing the output to get the unique strings, and finally parsing it back. It was quick enough to write but feels horrible.</p>
  
  <p>I'm sure there must be a better way than this. Any ideas?</p>